Java : Mysql timestamp daylight saving bug

Question

I have a particular table in mysql with a field refresh_time Type as timestamp.

In my code, I update the refresh_time field to a future date of next month. For that, I calculate the milliseconds for next month date using following logic :

periodRefresh = currentTime + MyServiceUtils.calculateNoOfDaysInMonth(currentTime)*86400000L;

And then the value periodRefresh (milliseconds) I convert to java.sql.Timestamp and pass it to db side to udpate the field :

new Timestamp(periodRefresh);

But what has happened was that the current date in db was 2021-04-03 15:57:13 and when the above logic was run to update the time to next month, same time, it updated the time as 2021-05-03 13:57:13 which is 2 hours less than expected.

Our server follows UTC timezone, but I see that the user was from Australia, and in that day (4th April 2021) there was a daylight savings time in AEST. But even then it doesn't add to above as if I convert 2021-04-03 15:57:13 UTC to AEST, it comes as 2021-04-03 01:57:13 and the daylight savings there is at 3 am.

All the above has confused me a bit with following questions:

If I am following UTC timezones, then also does sql.Timestamp and mysql can get affected by daylight saving time?
How is AEST daylight saving time affecting my Australia based user when I follow UTC timezone?
Even if DST was affecting, so it should have increased/decreased time by 1 hour, but how in my case a difference of 2 hours happened?

Timestamp knows nothing about DST or time zones. It's essentially just a number expressing an offset from an epoch. Whatever software displays it will interpret it in the context of the locale and time zone in which it's viewed — g00se
– g00se, Commented Aug 16, 2021 at 10:58
@g00se So, when I check in mysql table, the time in the field is 2 hours less than the expected. So, what could be the possible reason behind it? — arqam
– arqam, Commented Aug 16, 2021 at 11:00
mysql has an internal logic for daylightsaving see dev.mysql.com/doc/refman/8.0/en/time-zone-support.html use thqat — nbk
– nbk, Commented Aug 16, 2021 at 11:01
@g00se Its not about MyServiceUtils, if you see it just gives the number of days. But here the bug is in hours. — arqam
– arqam, Commented Aug 16, 2021 at 11:02

O. Jones · Accepted Answer · 2021-08-16 11:24:10Z

1

In MySQL, TIMESTAMP datatypes are always stored in tables as UTC. When you put them into the database, it first translates them from your connection's time_zone setting to UTC. And when you retrieve them it translates them the other way. This is not true of DATETIME data types.

This TIMESTAMP behavior is handy if you have a global app. You can ask each user for their preferred time zone, and store it as a user-preference setting.

If you take a raw (seconds since the UNIX epoch in UTC) TIMESTAMP value and add a month's worth of seconds to it, the resulting timestamp value may be in a different daylight-time regime. So it will be translated differently.

Either

do your timestamp processing in MySQL, for example with

UPDATE tbl SET periodRefresh = periodRefresh + INTERVAL 1 MONTH;

do it all in your app and use DATETIME, not TIMESTAMP, data types (to avoid the database's time zone translation, or
do it all in your app and use SET time_zone='UTC'; on every connection.

answered Aug 16, 2021 at 11:24

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

arqam Over a year ago

By the 3rd option, you meant setting time_zone='UTC' as @@session.time_zone; ? As connection setting timezone will be the server time_zone only right

Collectives™ on Stack Overflow

Java : Mysql timestamp daylight saving bug

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related