0

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.

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.

  • Thanks for the asnwer. what I want is for example you are in Europe, and u made some diary entries, and u move to states, i want the entries u made for example 8pm, to still appear as 8pm when u move to usa (change the timezone). this way we could just save as datetime (static way), but thing is that if user change timezone, and by the new timezone setting its already next day, user can track mood (make new entry again) in a just same day, thats what i dont want. so ya I need to use timestamps and somehow find a way to manage timezone from api maybe. – George Garchagudashvili Jul 24 '17 at 3:19
  • You can't have both. You either record a picture of the calendar and clock (DATETIME) or you record UTC (DATETIME). – Rick James Jul 24 '17 at 3:26
1

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.

  • Thanks for the reply. So even datetime fields are dynamic for timezones? And I'm using shared hosting will I still be able to do timezone imports? – George Garchagudashvili Jul 24 '17 at 3:21
  • and session scope sounds great – George Garchagudashvili Jul 24 '17 at 3:21
  • @GeorgeGarchagudashvili Until timezones loaded, CONVERT_TZ() function is unavailable. Just try to invoke it and you'll see. – Kondybas Jul 24 '17 at 10:24
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.