Πως αποθηκεύω UTC time σε MYSQL?

[babaliaris]

Μετά από αρκετό ψάξιμο, βρήκα ότι δεν μπορείς να αποθηκεύσης timezone times σε mysql χωρίς να χάσεις το offset time. Πολλοί όμως προτείνουν την παρακάτω λύση:

Αν έχει πχ ώρα Ελλάδος σε UTC 2021-04-25 00:04:30+03:00 το κάνεις split σε δύο κομμάτια date_time=2021-04-25 00:04:30 και offset=03:00
και στην Βάση αποθηκεύεις το date_time σε DATETIME column και το offset σε CHAR(5). Και για να τα πάρεις την UTC , απλώς διαβάζεις αυτά τα 2
πεδία από την Βάση και τα ενώνεις ξανά στην μορφή 2021-04-25 00:04:30+03:00.

Το θέμα είναι ότι όταν αλλάζει η ώρα πχ το καλοκαίρι (νομίζω αυτό λέγετε DST=daylight saving time) τότε αλλάζει και το UTC offset του κάθε timezone?
Δηλαδή αν το καλοκαίρι η ώρα Ελλάδος πάει 1 ώρα μπροστά τότε το UTC time γίνεται 2021-04-25 00:04:30+04:00 που σημαίνει ότι
το offset που έχω στην βάση έχει γίνει outdated? Αν ναι πως θα το λύσω αυτό το πρόβλημα;

Διάβασα από Wikipedia ότι το UTC δεν λαμβάνει υπόψιν του το DST αλλά δεν ξέρω αν αυτό σημαίνει ότι τα offsets δεν αλλάζουν.

Ευχαριστώ.

Επεξ/σία 26 Απριλίου 2021 από babaliaris

[k33theod]

Γεια babaliaris

Σε θυμάμαι από το python.org.gr 😄

Διαβάζοντας σχετικά στο documentation δεν νομίζω ότι υπάρχει το πρόβλημα που λες.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 5.1.15, “MySQL Server Time Zone Support”.

Αυτό σημαίνει όταν αποθηκεύσω ας πούμε ώρα 11 στην Ελλάδα θα πάει στην db 9 αλλά αυτό δεν μας ενδιαφέρει γιατί όταν την διαβάσω θα δείξει πάλι 11.  Δες και το https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html μήπως βγάλεις άκρη.

[babaliaris]

Τα TIMESTAMP θα σταματήσουν να δουλεύουν από το 2038 και μετά. Τελικά βρήκα μια λύση που νομίζω είναι top. Μετατρέπεις το date times σου se unix timestamp το οποίο εκφράζει χρόνο σε milliseconds και απλώς το αποθηκεύσης στην Βάση σε BIGINT πεδίο. 

 

 

Should I use the datetime or timestamp data type in MySQL? - Stack Overflow

Αναφορά σε κείμενο

Neither. The DATETIME and TIMESTAMP types are fundamentally broken for generic use cases. MySQL will change them in the future. You should use BIGINT and UNIX timestamps unless you have a specific reason to use something else.

Special cases

Here are some specific situations where your choice is easier and you don't need the analysis and general recommendation in this answer.

• Date only — if you only care about the date (like the date of the next Lunar New Year, 2020-02-25) AND you have a clear understanding of what timezone that date applies (or don't care, as in the case of Lunar New Year) then use the DATE column type.

• Record insert times — if you are logging the insert dates/times for rows in your database AND you don't care that your application will break in the next 19 years, then go ahead and use TIMESTAMP with a default value of CURRENT_TIMESTAMP().

Why is TIMESTAMP broken?

The TIMESTAMP type is stored on disk in UTC timezone. This means that if you physically move your server, it does not break. That's good ✅. But timestamps as currently defined will stop working entirely in the year 2038 ❌.

Every time you INSERT INTO or SELECT FROM a TIMESTAMP column, the physical location (i.e. timezone configuration) of your client/application server is taken into account. If you move your application server then your dates break ❌.

Why is DATETIME broken?

A DATETIME stores a DATE and a TIME in the same column. Neither of these things have any meaning unless the timezone is understood, and the timezone is not stored anywhere ❌. You should put the intended timezone as a comment in the column because the timezone is inextricably linked to the data. So few people use column comments, therefore this is mistake waiting to happen. I inherited a server from Arizona, so I always need to convert all timestamps FROM Arizona time and then TO another time.

The only situation a DATETIME is correct is to complete this sentence:

Your year 2020 solar new year starts at exactly DATETIME("2020-01-01 00:00:00").

There is no other good use for DATETIMEs. Perhaps you will imagine a web server for a city government in Delaware. Surely the timezone for this server and all the people accessing this server can be implied to be in Delaware, with Eastern Time Zone, right? Wrong! In this millennium, we all think of servers as existing in "the cloud". So it is always wrong to think of your server in any specific timezone, because your server will be moved some day.

Note: MySQL now supports time zone offsets in DATETIME literals (thanks @Marko). This may make inserting DATETIMEs more convenient for you but does not address the incomplete and therefore useless meaning of the data, this fatal issue identifie ("❌") above.

 

[k33theod]

1 ώρα πριν, babaliaris είπε

Τα TIMESTAMP θα σταματήσουν να δουλεύουν από το 2038 και μετά.

😄😃 και εμείς το ίδιο.

[babaliaris]

Εντάξει το βρήκα. Τελικά δεν χρειάζεται, διότι το ISO 8601 είναι πάντα σε UTC+00:00 . Δηλαδή δεν λαμβάνει υπόψην του καθόλου time offsets.

Απλώς στο application layer παίρνω το local time, το μετατρέπω σε UTC time και το αποθηκεύω στην βασή σε μορφή YYYY-MM-DD hh:mm:ss. Μετά όποιος και να πάρει αυτό το UTC time απλώς το μετατρέπει σε local time την τελευταία στιγμή (πρωτού εμφανιστεί στoν χρήστη) και θα δείχνει την σωστή local ώρα.

Πχ αν τώρα είναι 2021-04-25Τ2:37:00+03:00 ώρα αθήνας, σε UTC (ISO 8601) θα γίνει (- 3 ώρες) 2021-04-25Τ23:37:00+00:00 .Τώρα αποθηκεύεις αυτό στην βάση και όταν το ξανά διαβάζεις και το στέλνεις στην front-end εφαρμογή απλώς χρησιμοποιείς μια βιβλιοθήκη και το μετατρέπεις σε local time που αυτόματα χρησιμοποιόντας το locale του συστήματος του χρήστη, θα μετατρέψει 2021-04-25Τ23:37:00+00:00 στην δικιά του τοπική ώρα λαμβάνοντας υπόψην τα πάντα, leap seconds, time offset , DST.

Επεξ/σία 26 Απριλίου 2021 από babaliaris

[Papakaliati]

Στις 26/4/2021 στις 1:41 ΠΜ, babaliaris είπε

Εντάξει το βρήκα. Τελικά δεν χρειάζεται, διότι το ISO 8601 είναι πάντα σε UTC+00:00 . Δηλαδή δεν λαμβάνει υπόψην του καθόλου time offsets.

Απλώς στο application layer παίρνω το local time, το μετατρέπω σε UTC time και το αποθηκεύω στην βασή σε μορφή YYYY-MM-DD hh:mm:ss. Μετά όποιος και να πάρει αυτό το UTC time απλώς το μετατρέπει σε local time την τελευταία στιγμή (πρωτού εμφανιστεί στoν χρήστη) και θα δείχνει την σωστή local ώρα.

Πχ αν τώρα είναι 2021-04-25Τ2:37:00+03:00 ώρα αθήνας, σε UTC (ISO 8601) θα γίνει (- 3 ώρες) 2021-04-25Τ23:37:00+00:00 .Τώρα αποθηκεύεις αυτό στην βάση και όταν το ξανά διαβάζεις και το στέλνεις στην front-end εφαρμογή απλώς χρησιμοποιείς μια βιβλιοθήκη και το μετατρέπεις σε local time που αυτόματα χρησιμοποιόντας το locale του συστήματος του χρήστη, θα μετατρέψει 2021-04-25Τ23:37:00+00:00 στην δικιά του τοπική ώρα λαμβάνοντας υπόψην τα πάντα, leap seconds, time offset , DST.

Αυτός είναι ο αποδεκτός τρόπος, είτε unix seconds /miliseconds οπως επισης αναφέρθηκε. Γενικώς να αποφεύγεις να δουλεύεις και να αποθηκευεις εκτός UTC, σαν ο διαβολος το λιβάνι, μιας και είναι πολύ δύσκολο να δουλεύεις με timezones. 

Πάρε και ένα εξαιρετικό άρθρο πάνω στο θεμα: https://www.zainrizvi.io/blog/falsehoods-programmers-believe-about-time-zones/

ΥΓ: γιατί το παλικάρι με 4+ και 43 αναρτήσεις είναι advance member, και εγω ο κακομοιρος με 1751+ και 1462 αναρτησεις ειμαι ακομη junior;

Επεξ/σία 27 Απριλίου 2021 από Papakaliati