On Thu, Jul 21, 2022 at 08:50:45PM +0000, "????? ???????" wrote:
> But it didn't help me
That will only impact event you create after installing the TZv1 file.
Do youi still have the bug for newly created events?
If you want to fix the older events created while you had the TZv2 file,
you will need to edit the database. Below is the script I wrote to do
that. You will need to adjust at least the time zone (Europe/Paris in
my case) and the date after which event should be fixed, which is the
day you updated the system and got the TZv2 file (1587001800 in case).
You probably also need to adjust the charset.
I advise you to work on a copy of your SOGo installation. Make a backup,
run the script, check for bugs, fix, restore backup and start over.
Unfortunately there is no way to spot events that users moved back to
the appropriate time within the web interface. The script will
fix their time as if they were incorrect, relulting in an icorrect
time.
--- cut here ---
#!/bin/sh
# create an ic() helper function
mysql << EOT
DELIMITER //
CREATE FUNCTION ics(haystack mediumtext, field varchar(64))
RETURNS varchar(256) CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'
DETERMINISTIC
BEGIN
RETURN substr(haystack,
instr(haystack, field) + length(field),
instr(substr(haystack,
instr(haystack, field) + length(field)),
'\r') - 1);
END //
DELIMITER ;
EOT
for i in `echo "
select substr(c_location, instr(c_location, '/sogo/')+ 6)
from sogo_folder_info
where c_folder_type='Appointment';" | mysql -ABN sogo` ; do
echo "
update ${i}
set c_content = replace(
c_content,
concat(
'DTSTART;TZID=Europe/Paris:',
ics(c_content, 'DTSTART;TZID=Europe/Paris:')
),
concat('DTSTART;TZID=Europe/Paris:',
date_format(
convert_tz(
str_to_date(
ics(c_content,
'DTSTART;TZID=Europe/Paris:'),
'%Y%m%dT%H%i%S'
),
'Europe/Paris',
'UTC'
),
'%Y%m%dT%H%i%S'
)
)
)
where ics(c_content, 'PRODID:') like '-//Inverse inc./SOGo%'
and c_creationdate > 1587001800
and c_content like '%DTSTART;TZID=Europe/Paris:%';
update ${i}
set c_content = replace(
c_content,
concat(
'DTEND;TZID=Europe/Paris:',
ics(c_content, 'DTEND;TZID=Europe/Paris:')
),
concat('DTEND;TZID=Europe/Paris:',
date_format(
convert_tz(
str_to_date(
ics(c_content,
'DTEND;TZID=Europe/Paris:'),
'%Y%m%dT%H%i%S'
),
'Europe/Paris',
'UTC'
),
'%Y%m%dT%H%i%S'
)
)
)
where ics(c_content, 'PRODID:') like '-//Inverse inc./SOGo%'
and c_creationdate > 1587001800
and c_content like '%DTEND;TZID=Europe/Paris:%';
update ${i}_quick q, ${i} c
set q.c_startdate = unix_timestamp(
convert_tz(
from_unixtime(q.c_startdate),
'Europe/Paris',
'UTC'
)
),
q.c_enddate = unix_timestamp(
convert_tz(
from_unixtime(q.c_enddate),
'Europe/Paris',
'UTC'
)
),
q.c_cycleenddate = unix_timestamp(
convert_tz(
from_unixtime(q.c_cycleenddate),
'Europe/Paris',
'UTC'
)
)
where q.c_name = c.c_name
and ics(c.c_content, 'PRODID:') like '-//Inverse inc./SOGo%'
and c.c_creationdate > 1587001800
and c.c_content like '%DTSTART;TZID=Europe/Paris:%';
update ${i}_quick q, ${i} c
set q.c_nextalarm = unix_timestamp(
convert_tz(
from_unixtime(q.c_nextalarm),
'Europe/Paris',
'UTC'
)
)
where q.c_name = c.c_name
and q.c_nextalarm != 0
and ics(c.c_content, 'PRODID:') like '-//Inverse inc./SOGo%'
and c.c_creationdate > 1587001800
and c.c_content like '%DTSTART;TZID=Europe/Paris:%';
"
done
--- cut here ---
--
Emmanuel Dreyfus
[email protected]
--
[email protected]
https://inverse.ca/sogo/lists
