Managing timezones for user action datetimes

Question

I hope this is the right place to ask the following question, and I make enough sense what is my concern.

I have a table: mood_tracker, where I save about the feeling user has each day.

CREATE TABLE `mood_tracker` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mood_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `mood_date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mood_tracker_user_id_mood_date_unique` (`user_id`,`mood_date`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The thing is that user can use tracker app for each single day only once, so I depend on mood_date date field. But for example if I save date as UTC (any single static timezone) it will not work because users will be using different timezones.

My thing is I just got messed up with timezones, I'm building huge application where dates and times are very important things.

I'd really love to hear some suggestions how I can build schemas right way to make timezones flexible (adjust to user's timezone setting).

Thanks a lot.

Answer 1

Which do you want? Let's say you and I are in different timezones.

Should my 10:00 should show as "10:00" to you? If so, use DATETIME.

Should my 10:00 should show as "17:00" to you because we are 7 timezones apart? If so, use TIMESTAMP.

The only requirement is that our client machines be honest about what timezone we are each in. (And some config stuff.)

Meanwhile, get rid of the useless id and promote the UNIQUE composite index to be the PK.

Answer 2

First of all you have to import all the timezones from zoneinfo into the MySQL. There is special script called mysql_tzinfo_to_sql that do all the magic. Overall explanation can be found here: https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html After the zones has been imported you can use CONVERT_TZ(dt,from_tz,to_tz) function to convert raw datetime/timestamp in the UTC to the arbitrary TZ. Also your client can use SET time_zone = timezone; for the session and all the datetime/timestamp columns will be converted to the desired timezone on the fly.

Answer 3

From your comment I think there are separate tasks involved:

To record a user's diary entry with time, date, and timezone.

To know whether a user has made an entry "today" or not.

In the first part, datetime plus text will do it; show the recorded time zone code along with the captured date and time. You probably should include an indicator for daylight saving (clock adjustment in summer) - I never remember if that's +1 or -1. Let's see, in summer we get up at 7am instead of 8am, but we tell ourselves it is 8am. The daylight saving time is later than the real time.

If I was your user, I would be not too much puzzled to see that my input at 12:42 in London yesterday is called 07:42 since I am in New York now, but that's for you to choose.

The different question is whether the most recent input was yesterday or today where I am now. For that, I think you need to take the logged datetime, then add / subtract the difference between the user's timezone now (New York) and the timezone then (London), with daylight saving, to get the answer of 07:42am New York time anyway.

You know, there is a geographical International Date Line, although it mostly runs through the sea. Also there are space stations in orbit, but I don't know if they are your customers.