The automatic timezone conversion is the reason we DON'T use timestamp. It's also much easier to have everything be in UTC and convert as necessary (and explicitly), preferably on the client or client interfacing endpoints. Now we just have to worry about the year 10,000.
I tremble a little every time I hear SpaceX made some progress with their big starships. I just know we'll soon need to implement mars vs earth timezones, not to mention taking into account the communications delay based on the relative distance between earth and mars which is also based on the yearly orbits around the sun of both planets. I hope to be rich and retired by then :))
On a related note, there's also a 2040 problem with a bunch of old COBOL (and other language) programs on IBM systems. It's basically the Y2K problem again, but shifted by 40 years. This is a problem on IBM systems where they supported a 2-digit year in dates, but the 100 year period isn't 1900-1999. It's 1940-2039. There's a bunch of stuff out there that thinks that if the 2 digits are 40-99 then it's 1900+year, otherwise 20+year. There's even a lot of business logic that relies on 12/31/2039 being logically a "forever date", or a "no end date" in a from/thru date range. Maybe these systems will not survive another 15 years, but if they do then it might get interesting. Lots of major businesses still use these systems and this old code.
Working in the aerospace industry, I'm doing software engineering and need to account for long term storage of spacecraft.. so we need to support software well into the thirties, depending on the mission. So the 2038 issue is a real thing for us today.
The problem of using DATETIME is that you don't know from “where” (what timezone) it was stored, so it's difficult to detect the timezone (for created_at, updated_at, etc. that don't need country context, like birthdate, for example). It's the responsibility of backends to handle this and store it in a unified timezone (for example, UTC) *manually* . But for access from database directly (on another application/backend) you don't actually know in what timezone was stored that date. (For example, for an PHP application, you add another Node.js application and other team using Python, in different servers) For TIMESTAMP you *know* for sure it's in UTC. I think it is easy for interoperability.
not yours but in some other videos i see people mention y2k and say "it wasnt that much of a big deal after all" or similar stuff, totally unaware of the people who worked day and night to make sure everything just kept working.
Thank you! You really choose a topic that was causing me so much headache and I haven't thought of searching for a solution for it 😂, I got used to always dodge (and got really good at it) it by fixing my timestamp timezones manually at the back-end. Very insightful!
Heh, I always put my timestamps into varchar(14/20) columns, depending on whether I'm expecting mili/nano second accuracy to be required. Either way, it always seemed crazy to me storing it in something that fits so tightly in terms of space.
@@PlanetScale The application is the one who is responsible for all timing and sync, so it's usually de-facto an int, but it gets converted. The reason its varchar is because I need to be ready to support both INT and FLOAT, to varying degrees of accuracy, and I need the accuracy to be explicitly stated in the data (via a dot).
working with dates as months days and years etc is a nightmare. i prefer using UTC time with 64bit integer. easier to use and more predictable. and can go to negative which lets you represent any date from BC to AC
Unfortunately you don't get the benefits of built-in functions, also difficult to explicitly define that the integer represents a specific date range, whether it being a 64-bit epoch timestamp in seconds, milliseconds, microseconds or any other unit. Additionally, this is left up to application code to handle it, so as long as the DB and application code is tight, it's not a problem. But having multiple services can make this a headache in the future without proper specification.
@@dealloc True, if you don't have something in between, or proper specification. But, still better and worth it, if the code knows what its dealing with and that's how you deal with dates everywhere in every codebase. Even with DateTime, there is something weird about using a human readable date in the code or query that doesn't mean anything to you until you make it human readable again for the timezone you want.
All the fake dates in my web app's development database are from 2034-2036, we barely missed a Y2038 problem of our own making apparently. (We use future dates because filtering out things that occurred in the past is part of our functionality and using dates way in the future in dev data is the most solid way to make sure this functionality works in development)
Are there any plans to, I don't know, just add an extra byte to store more numbers in the timestamp column? Is Mysql official standpoint to migrate to an 8byte datetime column?
Great explanation. The use case of a library inventory with a "Due Date" column was my first thought for storing future dates. Would it be "better" to instead store "Checkout Date" and "Days Allowed", with overdue status being determined during a query? (Different users having different checkout period allowances)
If the only difference between TimeStamp and DateTime is the value being able to store and the auto UTC conversion. Wouldn't it just be easier from future databaes just to not use TimeStamp type? -
i was thinking whole time using date time that i was wrong people use time stamp so i must use it but now i was right the every time when i set datetime e
I like how he looked at a watch that didn't exist. I do the same thing. I'd wager that in the next 15 years, there will be a solution using some new tech we can't yet conceive.
Is it a bad idea to use a ISO 8601 as a varchar type instead of using a real datetime? I'm confused about how I'm supposed to set the time to UTC in my application framework. Do you mean like converting the date timezones with something like dayjs before inserting it into the db?
I would personally always store data in their most "honest" types. So if it's a date I'd store it as a date. Your application framework (Laravel, Rails, etc) should be able to set a connection timezone when connecting to the DB. That will ensure that you're always operating in UTC and not in some other random server timezone
In short: INTs and SERIALs are insecure (see Parler hack), UUIDs are slow, and ULIDs are faster than UUIDs and slower but more secure than INTs but are more obscure, have less compatibility, and do not have built-in generator functions or even type definitions in many databases (I think they do in MySQL if I'm not mistaken).
forget the actual problem. we need an actual cool-sounding acronym for this issue. Epochalypse is way too simple, Y2038 is not scary enough, Y2k38's length is an odd int.
@@PlanetScale I just think epochalypse is way to simple, and simplicity is forbidden in the software world. but I'm bad at cool acronyms, so, uhhhh, you win
Yup, storing as ints would work! It would require a bit of juggling in your queries, as they are no longer retain their semantic meaning as date+time, but that can be solved at the app layer.
The automatic timezone conversion is the reason we DON'T use timestamp. It's also much easier to have everything be in UTC and convert as necessary (and explicitly), preferably on the client or client interfacing endpoints. Now we just have to worry about the year 10,000.
May we live to see the year 10,000
@@PlanetScale If we are still using MySQL in the year 10,000 I hope we don't live to see that year
you guys still use MySQL?
@@NerdyWasTaken always have, always will
@@NerdyWasTaken I would like to use Postgres but not my decision.
I feel bad for developers that have to support timezones in different planets and galaxies
I better be retired by then
By that time, AI will do all that for us.
I tremble a little every time I hear SpaceX made some progress with their big starships.
I just know we'll soon need to implement mars vs earth timezones, not to mention taking into account the communications delay based on the relative distance between earth and mars which is also based on the yearly orbits around the sun of both planets.
I hope to be rich and retired by then :))
On a related note, there's also a 2040 problem with a bunch of old COBOL (and other language) programs on IBM systems. It's basically the Y2K problem again, but shifted by 40 years. This is a problem on IBM systems where they supported a 2-digit year in dates, but the 100 year period isn't 1900-1999. It's 1940-2039. There's a bunch of stuff out there that thinks that if the 2 digits are 40-99 then it's 1900+year, otherwise 20+year. There's even a lot of business logic that relies on 12/31/2039 being logically a "forever date", or a "no end date" in a from/thru date range. Maybe these systems will not survive another 15 years, but if they do then it might get interesting. Lots of major businesses still use these systems and this old code.
i see y2k problems i see employment ; desperate me
@@himanshutripathi7441 one person's failure is another's job security.
Working in the aerospace industry, I'm doing software engineering and need to account for long term storage of spacecraft.. so we need to support software well into the thirties, depending on the mission. So the 2038 issue is a real thing for us today.
I always store values as DateTime values, so I don't have to worry about the epochalypse anyway.
🧠
Even better store it as varchar, so not even y10k will be your problem :D
Same, all dates in my web app's database are stored as ISO strings. Big brain stuff.
Rather than using datetime why can't MySQL just add a timestamp64 type and we change our timestamp columns to that?
Not sure! It's possible they'll do something before doomsday, but for now just don't store future values in timestamps.
keep up the quality content. subscribed!
Thanks for this. I honestly didn't know about MySQL's 2038 problem until now.
very knowledgeable & I always get to know something I didn't know earlier, great content 💜
"you need to retire" .... best advice ever :D
Nice I just checked our DB and we have a timestamp column for reminder dates. Guess will change that to datetime soon. Thanks!
The problem of using DATETIME is that you don't know from “where” (what timezone) it was stored, so it's difficult to detect the timezone (for created_at, updated_at, etc. that don't need country context, like birthdate, for example).
It's the responsibility of backends to handle this and store it in a unified timezone (for example, UTC) *manually* .
But for access from database directly (on another application/backend) you don't actually know in what timezone was stored that date.
(For example, for an PHP application, you add another Node.js application and other team using Python, in different servers)
For TIMESTAMP you *know* for sure it's in UTC. I think it is easy for interoperability.
not yours but in some other videos i see people mention y2k and say "it wasnt that much of a big deal after all" or similar stuff, totally unaware of the people who worked day and night to make sure everything just kept working.
Very true! My dad was a DBA in the 90s and early 2000s, I bet he has some good stories there
Thank you! You really choose a topic that was causing me so much headache and I haven't thought of searching for a solution for it 😂, I got used to always dodge (and got really good at it) it by fixing my timestamp timezones manually at the back-end. Very insightful!
Heh, I always put my timestamps into varchar(14/20) columns, depending on whether I'm expecting mili/nano second accuracy to be required.
Either way, it always seemed crazy to me storing it in something that fits so tightly in terms of space.
varchar! That's wild! I'd say int, if anything.
@@PlanetScale The application is the one who is responsible for all timing and sync, so it's usually de-facto an int, but it gets converted.
The reason its varchar is because I need to be ready to support both INT and FLOAT, to varying degrees of accuracy, and I need the accuracy to be explicitly stated in the data (via a dot).
jeez i needed this vid, amazing as always Aaron
🫡 Here to help
What timezone does planetscale use? The timezone of the region the database is hosted in, or the timezone of where the request comes from?
Thanks for the video. Could you do one on using triggers? Thanks
I'll add it to my list!
all i got from this is that timestamps, which i didn't even know was an alternative to datetime, are inferior in every way. thanks! :D
Haha I'll take it! You're welcome
working with dates as months days and years etc is a nightmare.
i prefer using UTC time with 64bit integer. easier to use and more predictable.
and can go to negative which lets you represent any date from BC to AC
Unfortunately you don't get the benefits of built-in functions, also difficult to explicitly define that the integer represents a specific date range, whether it being a 64-bit epoch timestamp in seconds, milliseconds, microseconds or any other unit.
Additionally, this is left up to application code to handle it, so as long as the DB and application code is tight, it's not a problem. But having multiple services can make this a headache in the future without proper specification.
@@dealloc True, if you don't have something in between, or proper specification.
But, still better and worth it, if the code knows what its dealing with and that's how you deal with dates everywhere in every codebase.
Even with DateTime, there is something weird about using a human readable date in the code or query that doesn't mean anything to you until you make it human readable again for the timezone you want.
All the fake dates in my web app's development database are from 2034-2036, we barely missed a Y2038 problem of our own making apparently. (We use future dates because filtering out things that occurred in the past is part of our functionality and using dates way in the future in dev data is the most solid way to make sure this functionality works in development)
Haha, close call!
Are there any plans to, I don't know, just add an extra byte to store more numbers in the timestamp column? Is Mysql official standpoint to migrate to an 8byte datetime column?
Another problem with timestamp column is daylight savings. Eveything just moves by 1 hour twice a year. Fun
Great explanation. The use case of a library inventory with a "Due Date" column was my first thought for storing future dates. Would it be "better" to instead store "Checkout Date" and "Days Allowed", with overdue status being determined during a query? (Different users having different checkout period allowances)
I'd say anything that stores future values should probably be a datetime, to be safest!
The “retire” comment has resulted in me subscribing to the channel. Classic.
It's a foolproof plan!
If the only difference between TimeStamp and DateTime is the value being able to store and the auto UTC conversion. Wouldn't it just be easier from future databaes just to not use TimeStamp type? -
That's certainly an option! Datetimes are also twice as big as timestamps, so it's not totally "free"
i was thinking whole time using date time that i was wrong people use time stamp so i must use it but now i was right the every time when i set datetime
e
Can I get both a fun and a lame response?
Nice video btw, I really like your channel 😊
Thanks! I appreciate that! Make sure you tell your friends, so I can retire as soon as possible
PlanetScale, the corporation, appreciates your support. Please make sure to follow us on LinkedIn
(lame)
I got exactly what I asked for, no complaints here 😂
this channel is awesome lmaooo
So in 2038 people will finally update their MySQL?
one can only hope!
I like how he looked at a watch that didn't exist. I do the same thing. I'd wager that in the next 15 years, there will be a solution using some new tech we can't yet conceive.
Haha I do that all the time 😂 It's kind of habit at this point
Is it a bad idea to use a ISO 8601 as a varchar type instead of using a real datetime? I'm confused about how I'm supposed to set the time to UTC in my application framework. Do you mean like converting the date timezones with something like dayjs before inserting it into the db?
I would personally always store data in their most "honest" types. So if it's a date I'd store it as a date.
Your application framework (Laravel, Rails, etc) should be able to set a connection timezone when connecting to the DB. That will ensure that you're always operating in UTC and not in some other random server timezone
LOL incontrollably at solving the problem by retiring!
it's a foolproof plan 🧠
This is me feeling like a super hero after liking and subscribing to help you get out of the game sooner 😂
🫡 my hero
Love it.
first
What's the Mac App you're using?
TablePlus!
Pov Polish doctors of the National Health Fund (NFZ) setting your appointment for 20 years because there are no earlier dates XD
What about the Y1000 problem
does posgres also has this problem?
Not sure, you'd have to check the legal range of their timestamp column
I like your humor 😂
🫡 thank you!
You've calmed with this one. Imagine all those migrations to be done. Ahh the life of software engineer :D
🙂How about topic for regarding the use of int, uuid, or ulid in table primary key design?
On my list! Good idea
In short: INTs and SERIALs are insecure (see Parler hack), UUIDs are slow, and ULIDs are faster than UUIDs and slower but more secure than INTs but are more obscure, have less compatibility, and do not have built-in generator functions or even type definitions in many databases (I think they do in MySQL if I'm not mistaken).
why not change int to int unsigned?
Totally possible. You'd have to do a bit of type juggling with your queries, but that definitely works
4:15 🤣🤣🤣
forget the actual problem. we need an actual cool-sounding acronym for this issue.
Epochalypse is way too simple, Y2038 is not scary enough, Y2k38's length is an odd int.
What are you on about! Epochalypse is the coolest possible name! I dare you to come up with something funnier than epochalypse (impossible)
@@PlanetScale I just think epochalypse is way to simple, and simplicity is forbidden in the software world. but I'm bad at cool acronyms, so, uhhhh, you win
@@Dev-Siri win by default, I'll still take it.
2038 but not 2037? Weird
!remindme 15 years
*laughs in CockroachDB which goes until year 294276*
I'll definitely be retired by then
It just need to be updated
what needs to be updated?
I keep my dates as integers so should be good for abotu 18 days more till the 19th of jan 2038 :D
Buying yourself some time, smart
ORLY... the varchar language is guilty of range discrimination? Who couldn't have seen that coming?
What if we convert the timestamp cols into int cols? Shouldn't that remove the 4 byte constraint and the automatic UTC conversion?
Yup, storing as ints would work! It would require a bit of juggling in your queries, as they are no longer retain their semantic meaning as date+time, but that can be solved at the app layer.
your 34 you look mid 20s
unsubbing so you can't retire and we can have your videos for longer
Wait hang on