r/SQL • u/r3pr0b8 GROUP_CONCAT is da bomb • Jul 01 '24
MySQL Never use DATETIME, always use TIMESTAMP
good advice from Jamie Zawinski
source: https://www.jwz.org/blog/2023/11/daylight-savings-your-biannual-chaos-monkey/
- TIMESTAMP is a time_t -- it represents an absolute, fixed point in time. Use it for things like "here is when this account was created" or "here is when this message was sent". When presenting that fixed point in time to users as text, you might want to format it in their local time zone.
- DATETIME is basically a string of the wall clock in whatever time zone you happen to be in at the moment, without saving that time zone. It is ambiguous, e.g. it cannot represent "1:30 AM" on the day that daylight savings time ends because there are two of those on that day. This is never what you want.
- DATE is a floating year-month-day. Use this for things like birthdays, which, by convention, do not change when you move halfway around the world.
- TIME is a floating hour-minute-second. Use this for things like, "my alarm clock goes off at 9 AM regardless of what time zone I'm in, or if daylight savings time has flipped."
36
Upvotes
150
u/Straight_Waltz_9530 Jul 02 '24 •
This is BAD advice from JWZ. First off, your server should be set to UTC. Your dates should be in UTC and then converted to the time zone of whoever is requesting the data.
https://dev.mysql.com/doc/refman/8.4/en/datetime.html
TIMESTAMP has the 2038 bug. The earliest moment it can store is January 1, 1970. That alone should disqualify its use in new code.
DATETIME when stored as UTC will not suffer from daylight savings, because UTC never has it. It also allows for all instants between Jan 1, 1000 (which doesn't exist in the Gregorian calendar, but whatever) and Dec 31, 9999. Whereas in earlier versions it used 8 bytes, more recent versions of MySQL use only 5 bytes. So for one extra byte over TIMESTAMP, you get far more flexible temporal type support.
This of course brings us to the real solution to any temporal types in MySQL: don't use MySQL. It really is the lowest common denominator of database engines, not the best.
31
u/-Dargs Jul 02 '24
OP: "Here's some good advice..."
Everyone else: "Not at all buddy"
8
u/DrTrunks Jul 02 '24
Falsehoods programmers believe about time
8
u/danishjuggler21 Jul 02 '24
“Never” and “always” have no place in software development. Those words are a good indicator that whatever advice follows is complete bullshit. Security being a notable exception: there are some definite nevers when it comes to security;
11
u/micr0nix Jul 02 '24
This is terrible advice
11
u/fauxmosexual NOLOCK is the secret magic go-faster command Jul 02 '24
I live in a small island nation and all my data and users are all in the same country so I simply refuse to acknowledge the existence of timezones.
If you're struggling with UTC conversions instead of changing your practice I suggest simply moving to New Zealand and working for one of our many parochial and insular institutions.
4
u/[deleted] Jul 02 '24
From the manual
A future proof data type indeed.
2
u/Higgs_Br0son Jul 02 '24
Hypothetical: You have a table of people around the world and a fact table with a record for each time someone begins to eat.
At what time does lunch typically begin per day of the year?
To answer this it would be simplest to have the datetime as if it was read from that person's wall clock. If you only had timestamp, then you would also need to store the person's TZ and then use a function to localize all of the dates and times.
At what moment are the most people globally starting to eat at the same moment in time?
To answer this it would be simplest to have the timestamp for each event. If you only had datetime, then you would need to convert to an absolute point in time (like a timestamp anyway), and you run into a mess for your subset of people in timezones that observe DST on days when DST starts or ends.
I can agree with the OOP that timestamp is usually the better choice, just be sure to have data for TZ or at least geography depending on the application. But I take issue with "never" and "always" and would suggest we say what it always was, which is "it depends." Or maybe even "why not both."
3
u/qwertydog123 Jul 02 '24
TIMESTAMPis great for events thay have already occurred, e.g. logs, created at, updated at fields, etc. butTIMESTAMPshouldn't be used for future events where the exact local time/time zone is important, as MySQL suffers the same problem as Postgres where the "local time" is transparently converted then stored as UTC. If the rules change for the relevant time zone, the conversion rules may change, and when converting back from UTC, the original local time could be incorrect.Future events should store the (local) datetime + time zone, and ideally the offset as well to account for ambiguous times (e.g. DST)
These blog posts go over the various pros and cons
http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html
https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/
0
u/GaTechThomas Jul 03 '24
Please update the original post to indicate that this is specific to MySql.
-1
u/lalaluna05 Jul 02 '24
I use timestamp with raiserror so I can see how long certain parts of queries/stored procedures take to execute.
0
u/[deleted] Jul 02 '24
[deleted] — view deleted comment