0

I run a toy database where users can browse around, find toys they own, and "add them to their collection". On the back end, I add a row to a dedicated MYSQL table with data about the collected item, and one of the fields is date_bought, of type "DATETIME". It sounds simple enough, but various issues and uncertainty have been plaguing me around this for years, and I can never seem to find anybody discussing the exact problem I'm having even though it seems like the kind of thing that would impact everyone doing something similar, so I must be missing something hugely obvious.

In the UI for editing a collected item, there is a simple date picker. If you were to pick today, for example, you would enter "05/27/2023". In the client JavaScript code, I basically create a new Date object with the requested date, and 0-out the hours, minutes, and seconds (making it midnight). The back end receives the date in ISO format, and I write it to that date_bought field.

So far, so good (or not). It gets tricky when we decide to display the date information on the "view my collection" page. When I go to display that "date bought" data point, I deliver the value in ISO format, and display it in local format.

Here's the issue: it works fine when you're viewing a date you entered in your own timezone, but there can be weirdness for people viewing the same page in a different timezone, even neighboring ones. Let's say for example that Person A, living on the East Coast, tags an item that he owns, and sets "date bought" to 05/27/2023. That's midnight his time. Let's say that person B, living on the West Coast, views the same page; his midnight happens 3 hours later than the person on the East Coast, so he would see "05/26/2023" for that item instead of "05/27/2023". This is technically correct, I suppose, but totally unintuitive--"today" should basically mean the same thing for them both in a casual sense, so they should both see "05/27".

So, here comes the questions:

  1. Should I even be using a DATETIME if I only really care about the date component? Would using a DATE simplify any of this?
  2. Is there a best practice or technique for mitigating this kind of problem? In the past I've tried artificially adding an extra 12 hours before sending to the client for display so that people in nearby timezones won't see weird artifacts around the edges like I describe here; it basically works, but it feels like a weird hack and I have to think there's a better way.

Please let me know if you have any insights, and thanks much.

1 Answer 1

0

MySQL has two datetime storage types:

DATETIME is like taking a picture of your clock and storing that in the database. Others will see what your clock said. You store 1:00; they see 1:00.

TIMESTAMP is a time in the universe. Technically, your clock is converted to UTC as you store it in the database. The conversion is based on the timezone the machine is set to. When your friend fetches that timestamp, it is reconverted based on their machine's timezone. You stored 1:00; they see 2:00 for example.

There are various types of clock needed, but those are two made available in MySQL.

There is a "LOCALE" setting that can be used to change your timezone temporarily. Perhaps this is what you are looking for?

2
  • Thanks, but I think this problem exists regardless of what date type is used; the question is how do you express a consistent "today" feel across nearby timezones...
    – Max L
    May 28, 2023 at 4:52
  • StackExchange collects stats on us. They define a "day" as starting at midnight UTC.
    – Rick James
    May 28, 2023 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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