dotCMS
UT Task210901UpdateDateTimezones not running successfully
Customers upgrading to 22.03 LTS are seeing issues with their time values, it seems that this task is not running as it should.
We need to check the following:
1- it’s changing the column type but not updating the values, need to investigate why?
2- it’s only updating the date fields, we should update the mod_date
3- Even if run, we think the offset has to be multiple by -1, this is because it tells us how many seconds our timezone has a difference between UTC, so we need to add/subtract this accordingly.
Ask support for dataset to test this.
Acceptance Criteria
- Database should end in the right timezone after run the task
- Check the date and time before and after the update
For current customers that experience this issue we need to:
1- Run this query to get the customer timezone:
select timezoneid from user_ where userid='dotcms.org.default';
2- Go to https://dateful.com/time-zone-converter and check the hours diff between timezones, e.g between UTC and EST is -5 hours (meaning EST is 5 hours behind UTC)
3- run the following queries, modify as needed: (one for each date field and also for mod_date), this contentlet_as_json is null because we want to update only the old contentlets, new ones should have the right time.
update contentlet set date1 = date1 + interval '18000' SECOND where date1 is not null and contentlet_as_json is null;
Need to consider change timezone e.g EDT/EST. https://dotcms.zendesk.com/agent/tickets/107650
Hey team! Please add your planning poker estimate with Zenhub @hmoreras @jcastro-dotcms @jdotcms @rjvelazco
We need also to update syspublish_date and sysexpire_date of the identifier table
We need some work here, after testing multiple times locally, with a real data set we are unable to get the expected results.
Fixed, finally we are able to run de upgrade and get the corresponding date/time in each piece of content.
Tested in a real database in postgres // Release-22.10