Mysql – Managing timezones for user action datetimes

MySQLtimezone

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.

Best Answer

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.

Related Question