Until now I have always set my time format to “locale”. Unfortunately, there is no description in the documentation of what this setting means. Anyway, I’ve now discovered that there is a problem here: for example, I store 4:00 p.m. Central European Time in the database and when I open this record again later, it shows 2:00 p.m. How can I ensure that the time I have saved is displayed in the same format/timezone again later (i.e. 4pm) ? I also tried using UTC as the datetime format. If I save the date and display it later, the wrong time is still displayed (2 hours earlier). So how can I achieve my goal?
Save it where? And what’s the mechanism for saving? This smells like saving a local representation of the date, but without a time zone, so the backend saves it as if it were in GMT, and then when you read it back, you have that 2 hour corruption.
The way to handle this best is to make sure that you save your data in UTC (which is timezone-less) but display it in local representation.
If you do date now in Supabase e.g predefined date field, you’re indeed suffering from your server’s time zone “corruption” as raydeck stated. I suggest sending timestamps from the client with an explicit timezone.
In one of my first Office Hours I was instructed to use toDateISO for converting my datetime into the timestampz field format of Supabase. But this seems to cause the issue.
In the examples below, the deadline is set to 17:00 CET. I’ve now used this formula and the datetime is correctly saved in Supabase (I don’t know what effect the “true” has but…):
If I edit the datetime, it is correctly displayed:
However, the deadline displayed in my projects list is wrong:
I tried many different formulas…
Or
… and others but none displays the correct time (17:00).
Note: Displaying the saved date without any formula displays the correct time:
But I want to display it in the “YYYY-MM-DD HH:mm” format. However, using the formatDate function produces the wrong time:
I’m totally lost!
Could anybody tell me how to achieve my goal:
- I’m entering a DateTime in my timezone (e.g. 2024-06-20 16:00).
- I save this date in a Supabase table in UTC format.
- If anybody in any timezone reads the DateTime it should be displayed in their timezone format (timezone determined by browser or OS setting).