CODEWITHSUNDEEP
mysqldatedatetimetimedatetime-format
Rehan Adil
Rehan Adil

Reputation: 47

What's the best data type for DATE and TIME in MySQL database to display time RELATIVE to the user?

enter image description here

For instance, I have a blog where users can comment and I want everyone can see how long ago the comment was posted, for example: 5 minutes ago OR 3 hours ago.

So if a guy in London posts a comment and a guy in India visits the page, they both should see "1 minute ago" and on hover should see the time relative to their timezone. (10pm in London, 3.30am in India).

My current solution in mind is to use varchar(25) data type and store the time as ISO-8601 (e.g. 2019-12-12T21:46:42+00:00)

Using this I can get the timezone of the commenter and convert the time to the current user's timezone. It works perfectly.

But I wonder if there is a better / more elegant way to do it?

So far I tried using DATETIME and TIMESTAMP data types but they do not seem to be useful in this scenario. I read online that TIMESTAMP is supposed to store time in UTC timezone and send it back in user's timezone but that did not happen for me, it got saved in my local time instead. And yes, I did not specify any time while saving data, MySQL used the CURRENT_TIMESTAMP.

enter image description here

Any thoughts or ideas?

Upvotes: 1

Views: 488

Answers (1)

Aran
Aran

Reputation: 2487

I'd recommend storing all your dates/times in one universal format in your database and UTC would be the best candidate for this.

That way, regardless of their location, it's easy for you to say 1 minute ago...

If you need to display the full date/time on the front-end, you'd need to convert the time from UTC to that user's location, which you can do via PHP's handy DateTime functions:

https://www.php.net/manual/en/datetime.settimezone.php

Upvotes: 4

Related Questions