Dates and localization

7
I had a problem that the dates in the database were always an hour behind leading to a date discrepancy. The client would show 1/1/2012 but in the database it would be 31/12/2011. I fixed this by turning off the localization for this attribute and now the dates are accurate. However the system generated dates createdDate and changedDate are 2 hours behind - even though the server time equals my local computer's time. Further when importing a date from a different database using a java action the date is inaccurate by one day again. The date in the source database is 31/05/2011 after importing it becomes 30/05/2011. This attribute has also localization disabled. After enabling it the date is correctly displayed as 31/05/2011 but still incorrectly stored in the database which means that something is wrong with the import. Why does one attribute need localization while the other doesnt. Both databases are on the same server. Please help Update I just checked the java action and the date fetched when using toString() is still correct. So it must be while committing. The debugger also displays 31/05/2011 even though in the database its 30/05/2011 what is going on. Update 2 Lets concentrate on the createdDate and changedDate. Double checked the database server's time and the web server's time. both are the same and correspond to my laptop (local time). When I save a record however no matter whether from local browser or webserver browser the time is 2hours behind. What else can I check? Solution as Bas suggested this issue was caused by java applying a local timezone. This is the code to be used in a java action: *object*.setDate(rs.getTimestamp("DATE", Calendar.getInstance(TimeZone.getTimeZone("UTC")) ));
asked
4 answers
6

Also bear in mind that 'turning off' date localization for an attribute only affects how it is displayed in the client browser, not how it is stored in the database.

The date-time will always be stored in the database as UTC. If you have localization turned on, the display of this date will be adjusted to take account of your timezone. If localization is turned off, the date displayed will be just UTC time. One further complication is to do with daylight savings...

If you look into the future (or past) at a date that was saved when daylight savings is different from how it is now (like now is summer time, look at a date later in the year after the clocks go back in the autumn), you will see one hour more(or less) offset than you expect. That is because the effect of daylight savings is to move you to a different timezone (you have a different UTC offset) for part of the year.

You can really give yourself a headache thinking to much about these things. It's a bit like relativity - how the date looks depends on where you are looking at it from. But at the database level it is always simple - always stored in UTC

Update: I have checked on a server located in California (PST) viewing the dates from both the UK (GMT, currently UTC + 1) and from the server itself (PST, currently UTC - 7). Here is the result: alt text

As you will see, both the system createdDate and a custom create date are exactly the same in the database. The system createdDate is ALWAYS displayed in UTC. The create date localized field is displayed adjusted for the correct client timezone.

answered
2

Could it be that when importing that date from another database, you're treating the representation of the date as a localized date in your java action when it should be UTC?

For more insight in Dates and times in Java, read http://www.odi.ch/prog/design/datetime.php

answered
0

I just checked the java action and the date fetched when using toString() is still correct. So it must be while committing.

answered
0

I suspect your issues with createdDate and changedDate are explained in this thread.

answered