How to get rid of (weird) different time zones in database

5
I know several questions have been posted on this forum about dateTime values and localization and/or timezone. However, I can't apply any of the answers to my problem, so bear with me... We've had an application running for well over a year. First on version 2.4, but now we've migrated to 2.5. The application contains an Employee entity with a BirthDate attribute of DateTime, which is edited using a normal date picker widget (thus losing the time component). If I look in the database using pgAdmin III (we're running PostgreSQL), the attribute is stored in a column of type 'timestamp without time zone'. In the database, I do see a time component, mostly set to 23:00:00. However, there are also records that are set to 22:00:00. I guess this is because some records were created when Daylight Saving Time was active. However, this creates date errors in some parts of the application, because they are interpreted as 1 day earlier (i.e. when using formatDateTime($BirthDate, 'dd-MM-yyyy')). I created a conversion microflow to try to get rid of the differences in time zone (Daylight Saving Time). I tried to do this by adding 2 hours, using formatDateTime with 'dd-MM-yyyy' to go to a string and parsing the string back into a DateTime value. However (and this is the weird part), the application seems to remember the time zone which was used!? Even if I save this date to a different attribute, it still retains the Daylight Saving Time, which shows when logging the DateTime using 'dd-MM-yyyy HH:mm:ss.SSS Z'. Even manually updating the database using pgAdmin using the query below doesn't help. update hrm$employee set birthdate = birthdate + interval '2 hours' where date_part('hour', birthdate) = 22; update hrm$employee set birthdate = birthdate + interval '1 hours' where date_part('hour', birthdate) = 23; The logging still shows the following: 17:56:43.000 HRM Employee A, birthdate: 01-09-1967 01:00:00.000 +0100 17:56:43.000 HRM Employee B, birthdate: 01-03-1988 01:00:00.000 +0100 17:56:43.000 HRM Employee C, birthdate: 17-04-1989 02:00:00.000 +0200 17:56:43.000 HRM Employee E, birthdate: 03-12-1963 01:00:00.000 +0100 What is happening here? Where does the application get this Daylight Saving Time offset from? Any ideas?! Kind regards, Jonathan van Alteren - FlowFabric
asked
2 answers
5

Let me start off with saying that DateTime logic has been rewritten in 3.0 because we wanted to get rid of server timezone influence. The server should no longer have anything to do with dates and times anymore (except for scheduled events)

However, what you are seeing is what I would expect. I am guessing the 'localize' boolean is set to true on this datetime attribute? This is something that should be false for 'real' dates such as birthdays. In your current situation it SHOULD store dates in summer at 22:00 the previous day and in winter at 23:00 the previous day. This is the offset that the (in this case) server is at from UTC when saving this time and it will know which timezone is applied because it knows at which date DST goes into effect.

In 3.0 we've made more improvements where a user can set their own timezone and this is used for date calculations on the server instead of the server's own timezone (including the microflow formatDateTime function). This could be causing some of your confusion but I don't think it applies in this case as your server timezone is probably the same as your client timezone.

The correct approach is to set 'localize' to false. You will have to update the birthdays in the database that were already inserted with the incorrect setting yourself afterwards (they should then be stored at the actual date at 00:00 hours)

The differences in birthday offset you are seeing in your logging are because of DST being in effect at that date or not. You'd say employee A also has DST in effect but the Netherlands did not follow DST in 1967, it was reintroduced in 1977.

answered
-1

I think you get the DST because the server is running in a timezone as well. Is the result correct if you use the UTC version of the function to log the dates?

And if you use trimToDaysUTC when storing the day?

answered