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.