Retrieve dates at the correct time irrespective of DST

Question

Language: Java
Database: mySQL

I have to build an alert system for users which will send notifications at certain times. For example, the user can specify the time 7am and date May 1, 2020 which means the alert has to be sent at May 1, 2020 at 7am. The user can be in any part of the world so we have to factor in the different timezones. I originally thought that we had to store the times in UTC. So if I'm a user in the EST time zone, for example, the db would store May 1, 2020 for the date and 12pm for the time(I plan to store the date and time in separate columns). However, now I'm confused because if I had stored the date last week, i.e., before daylight savings the UTC time stored would have been May 1, 2020 at 11am. So the user won't get alerts at the right time when DST occurs. How do I store the time so that the user always gets the alert at 7am their time? Do I have to store the date in the users local timezone? I have a table which stores the timezone info of the users by the way.

You store the value in the database in UTC, but you *calculate* the value using a `ZonedDateTime` with the user's time zone. — Andreas, Nov 02 '20 at 22:31
@Andreas Not the solution when it has to trigger in local time, whose definition can change. — chrylis -cautiouslyoptimistic-, Nov 02 '20 at 22:33
@chrylis-cautiouslyoptimistic- By "whose definition can change" are you thinking of changes to the time zone database? Otherwise, what "definition" are you thinking of? — Andreas, Nov 02 '20 at 22:36
@Andreas I'm not sure how that would work considering DST changes. Say there are two users; both of them want their alerts triggered at May 1, 2020 7am with the time zone "America/NewYork". But one user stores the UTC value before DST and the other stores the same time after DST. Won't the two UTC values stored in the db be different? — Coder95, Nov 02 '20 at 22:37
@Khazana With `ZonedDateTime`, there is no "before DST" vs "after DST", because `ZonedDateTime` *knows* whether any given date/time is in Daylight or Standard time, e.g. it knows that May 1, 2020 7am is Daylight Savings Time. *That* is why you should use `ZonedDateTime`. — Andreas, Nov 02 '20 at 22:39
@Andreas That's _precisely_ what I'm referring to. Converting a local time to UTC is a lossy operation. — chrylis -cautiouslyoptimistic-, Nov 02 '20 at 23:34
I don't understand the question. If you create a `LocalDateTime` and then convert it to an `Instant` specifying a timezone, you get the UTC point in time. That `Instant` when converted back to that same timezone will be the local date/time you specified. The point at which the `LocalDateTime` was _created_ has no bearing on this. — Jim Garrison, Nov 02 '20 at 23:53
_"Converting a local time to UTC is a lossy operation"_ -- this is incorrect if you start with a local date/time, convert to UTC, and then convert back using the same timezone specification. Let's call the local date/time `t`. The DST offset used in both cases will be the one in effect at time `t`, even if you performed the conversion at a different time with a different current DST offset. — Jim Garrison, Nov 02 '20 at 23:58
@JimGarrison are you saying that the Instant will be the same regardless the offset? My understanding is that the timezone conversions(to UTC) uses offsets from UTC. I'm observing that my ORM Hibernate adds 5 hours to a date this week whereas it was only adding 4 hours three days ago(DST started Nov 1 here). So, how will the UTC values be the same? — Coder95, Nov 03 '20 at 00:19
Say your event happens at 07:00 local time on Oct 31 2020. You convert this to an `Instant` applying the user's local timezone. Assume it's US Pacific Time. At that instant in time, PDT applies, so the offset is -0700, and the UTC is `2020-10-31 14:00Z`. That will never change, no matter when you do the conversion. Now you want to calculate the time one week later. You take that date/time, convert to `LocalDateTime`, add the week, and convert back to UTC. After you added the week, the new local value is `2020-11-06 07:00` but since DST is not in effect, the offset is -0800... — Jim Garrison, Nov 03 '20 at 01:03
... and the UTC is `2020-11-06 15:00Z`. Again, that will never change. — Jim Garrison, Nov 03 '20 at 01:05
A related question that may or may not answer yours: [Storing appointments in a SQL database such as Postgres for use with java.time framework](https://stackoverflow.com/questions/64545555/storing-appointments-in-a-sql-database-such-as-postgres-for-use-with-java-time-f). — Ole V.V., Nov 03 '20 at 19:32

Arvind Kumar Avinash · Accepted Answer · 2020-11-02T23:36:43.887

Use `LocalTime`

Check the following introduction of LocalTime:

This class is useful for representing human-based time of day, such as movie times, or the opening and closing times of the local library.

A date-time with timezone or zone-offset is the wrong choice for this use case. I am assuming that you have the users' timezone in your database and you have scheduled some corn job to send the notification. When the job runs to send the notification, simply check which users have a local time of 7:00 am (plus/minus a few minutes e.g. 5 minutes as per your requirement and frequency of the corn job) at that time e.g.

import java.time.LocalTime;
import java.time.ZoneId;

public class Main {
    public static void main(String[] args) {
        LocalTime time = LocalTime.now(ZoneId.of("Europe/London"));// Your server's timezone
        System.out.println(isLocalTimeSameAsNotificationTime(ZoneId.of("Asia/Calcutta"), time)); // User's timezone
    }

    static boolean isLocalTimeSameAsNotificationTime(ZoneId zone, LocalTime notificationTime) {
        LocalTime time = LocalTime.now(zone);// Local time at the param zone

        // True if +/- 5 minutes notificationTime
        return !(time.isBefore(notificationTime.minusMinutes(5)) || time.isAfter(notificationTime.plusMinutes(5)));
    }
}

The query in the corn job should be something like SELECT userId, user FROM tableName WHERE notificationTime = now plus/minus 5 minutes and now you have to loop through this resultset. For each record in the resultset, you need to call the function, isLocalTimeSameAsNotificationTime passing the timezone and server's local time as params and trigger the notification if it returns true.

Note: The SQL query I have mentioned here is for an example of how you have to write the query; it's not syntactically correct.

Thanks. You're right that I plan to set up a cron job which will keep checking the time every minute. I thought that this cron job would check its time (in UTC) and look for matching times(which should also be in UTC) in the users database to send alerts(thus not looping through every user). But with your code, it seems like we have loop through all the users every minute and then check if `LocalTime.now(zone)` is equal to their alert time. I wonder if we can restrict the checking of `LocalTime.now(zone)` to one place or in other words unify the timezone to avoid overhead during processing. — Coder95, Nov 02 '20 at 23:11
@Khazana - You do not have to loop through all users. The query in the corn job should be something like `SELECT userId, userTz FROM tableName WHERE notificationTime = now plus/minus 5 minutes` and now you have to loop through this resultset. For each record in the resultset, you need to call this function, `isLocalTimeSameAsNotificationTime` passing the timezone and server's local time as params and trigger the notification if it returns `true`. **Note:** The SQL query I have mentioned here is for an example of how you have to write the query; it's not syntactically correct. — Arvind Kumar Avinash, Nov 02 '20 at 23:31
Thanks! I think we can move the entire logic to the sql query like so : `SELECT userId where notification_time = CONVERT_TZ(now() , 'UTC', tz);`. But I think the key takeaway from this discussion is that we shouldn't store the time in UTC. — Coder95, Nov 03 '20 at 00:28

O. Jones · Answer 2 · 2020-11-02T23:01:15.563

The MySQL TIMESTAMP data type is perfect for this problem. They are always stored in UTC (as long as your server's OS isn't set up bizarrely time-zone-wise). As long as the OS's clock is in UTC, you'll be in good shape.

Then when you give MySQL commands like these:

   SET time_zone = 'America/New_York';
   INSERT INTO notification (deliver_timestamp, '2020-11-20 11:30:00')

On the way in to store a TIMESTAMP MySQL converts it from the current time to UTC. And on the way out it converts it back to local time. So, if you do this, for example.

   SET time_zone = 'America/Los_Angeles';
   SELECT timestamp FROM notifications;

you'll get 1010-11-20 08:30:00 which is the same time, but three hours behind. The strings like America/New_York and Asia/Kolkata are the names of the time zones. MySQL and other software that uses this sort of zoneinfo timezone name knows all the electropolitical stuff about when various places switch from daylight to regular. Not to mention historical changes to those rules going back more than half a century. It's a sophisticated system.

To get this to work

To get this to work globally you must have a Timezone user-preference setting for each person who uses your system. You'll have to rig up some sort of dropdown menu that shows the timezone names from the zoneinfo data base. You need that zoneinfo name for MySQL's SET time_zone='zoneinfoname' command. You can use these same text strings with the java.time.ZonedDateTime class.

Then, each time you access your database on behalf of a user, you set the time zone before you do anything else. In a typical web app each user's timezone preference is part of their user profile.

Notice: only TIMESTAMP columns work this way. DATETIME columns store and retrieve whatever time data you give them, without any notice of time zones.

And, if your Java code needs UTC time, do this.

   SET time_zone = 'UTC';
   SELECT timestamp FROM notifications;

It can be a little hard to wrap your head around this. MySQL uses the zoneinfo rules for these conversions. Zoneinfo knowswhen the time is DST and when it is not.

Thanks! But if you insert `SET time_zone = 'America/New_York'; INSERT INTO notification (deliver_timestamp, '2020-11-20 11:30:00')` at times(think date created time) with and without DST, won't the values stored in the db end up being different due to the different offsets? When I say time, I don't mean '2020-11-20 11:30:00' but the time of the insertion. — Coder95, Nov 02 '20 at 22:55

Retrieve dates at the correct time irrespective of DST

2 Answers2

Use LocalTime

To get this to work

Use `LocalTime`