64-bit unix timestamp is not supported in MySQL functions (2005) | Hacker News Reader

64-bit unix timestamp is not supported in MySQL functions (2005)

bugs.mysql.com

82 pointstobyjsullivan38 comments

Note - MySQL Have 2 data types. One is TIMESTAMP which is signed 32bit timestamp which is very useful for times around now. There is also DATETIME type which has broad range and this is what you should use in general

MySQL has more unfortunately-named time-related types than Python, which IMO is quite the achievement:

   - TIME
   - DATE
   - YEAR
   - TIMESTAMP
   - DATETIME

decades of history and following decades old standards from the start of computing in the 70s, 80s.. will do that to you.

They should probably map to DATETIME internally but it inefficient for storage.

Decades of MySQL not following standards you mean?

What you need is the Allen Algebra over intervals so you can represent, say a "week", as an interval from a start and an end.

Whoever set up the database for my current work project apparently thought that wasn't enough, because it's got a whole lot of columns called "dateTimeStamp" which--on inspection--turn out to be VARCHARs containing a string representation of a timestamp. In ISO 8601 format. The one that looks like "2019-12-11T00:04:37.754329Z".

Yes, they actually have six decimal digits of precision. For tracking boxes on a warehouse conveyor belt.

Maybe they worked for NATO in a previous job?

The precision is likely the default of whatever instrument recorded the timestamps. At this point, however, having to use VARCHARs instead of some native representation of ISO 8601 is entirely the fault of Oracle. It has been a published standard for over 30 years and should be directly supported.

> At this point, however, having to use VARCHARs instead of some native representation of ISO 8601 is entirely the fault of Oracle.

I wish I could blame Oracle. We're using Watcom SQL on a Sybase DB.

First time I've seen someone complain about finding standard ISO 8601 in a database.

This particular database is mainly used to display results to humans, and whoever wrote the original code didn't bother to convert it from 8601 to human-readable (one of the things I'm fixing), so it stands out.

>than Python, which IMO is quite the achievement

Python appears to only have date, time, datetime, and timedelta, which doesn't seem excessive when you consider that there are times when you only want date or time by themselves.

Honestly Python needs more, Joda/Java 8 had the right idea that zoned times are a different concept than a local one and you need to explicitly convert to get one from the other.

In my view, ZonedTime<Z> needs to exist, such that it isn't even a complete type without an actual time zone, and you can convert between ZonedTime<PDT> and ZonedTime<CEST> with a cast.

Of course, I'd also have DurationTime as its own type, and do whatever magic is needed to ensure it remains uninfected by leap second shifts.

I used to think that way - just treat date times like string encoding. Pick your desired format (UTC/UTF-8), convert into it on input, convert from it on output as needed.

But it turns out there are situations where it does matter. If you ever run into a multi-timezone calendar problem, this approach breaks down.

A couple of examples of where it doesn't work:

* My alarms needs to wake me up at 8am. If I am at home, this should be 8am Irish time. If I relocate myself to the US, it should be 8am in whatever US timezone is currently in effect for my location.

* I want to schedule something to happen all day Irish time next Christmas. From 00:00 to 23:59. In the meantime, the EU actually abolishes DST: https://www.npr.org/2019/03/27/707179979/european-parliament... - The UTC conversion of 2020-12-25T00:00:00[Europe/Dublin] today gets converted to 2020-12-25T01:00:00[Europe/Dublin] when you try invert it in christmas 2020.

Both these situations are resolved with local dates/times. The first one should just be a local date, while the second one requires the combination of the local date and the timezone to happen at time of check, not at time of input.

> I used to think that way - just treat date times like string encoding. Pick your desired format (UTC/UTF-8), convert into it on input, convert from it on output as needed.

Not quite what I had in mind, but I suppose you could serialize a ZonedTime<Z> to a string, like you can serialize an integer to a string.

> Both these situations are resolved with local dates/times. The first one should just be a local date, while the second one requires the combination of the local date and the timezone to happen at time of check, not at time of input.

Right. The first example requires a different type, LocalTime, and the second example requires types to change interpretation over time. Neither of these are problems with my scheme. They're problems with UI and system administration.

But you can have TZ aware and unaware datetimes in Python. It's not the best API but it's there

None of which are time zone aware. Potsgresql on the other hand...

Is this the only part of MySQL/MariaDB that is known to have a Year-2038 issue?

Yes. UNIX_TIMESTAMP(), FROM_UNIXTIME(), and the TIMESTAMP datatype. Everything else time related appears to be fine.

Going to be an interesting birthday for me that year. Still surprised that this hasn’t been fixed yet, we’ve known about it for a while.

Apparently, it's also an issue with MariaDB.

I don't know, but I also don't know why anyone didnt make the switch to MariaDB yet...

Ignorance is bliss ?

Non of the top Internet dogs - Facebook, Twitter, Uber run MariaDB

There's at least one that does.

https://www.theregister.co.uk/2013/09/12/google_mariadb_mysq...

And Wikipedia: https://blog.wikimedia.org/2013/04/22/wikipedia-adopts-maria...

Some do. But MySQL is far much more popular on large scale environments.

In Google BTW I'm not sure how much MySQL/MariaDB is left at all. They have been moving most of the stuff to Spanner.

Performance most likely.

Fear of breaking stuff. We still have some MySQL 5.1

Ran into this issue on a project I work on :P

This is going to be great for programmer job security in 2036/2037 if we just keep it hush hush for awhile longer.

We just added in some new TIMESTAMPs today. Gotta make sure the code is maintainable in the future you know.

2038consulting.com is my long-con / retirement plan, but I'd be happy to have this as a solved problem in the next decade instead.

Reason 34,583,498 to use Postgres instead of MySQL.

Also to not use the TIMESTMAP type in general, which is full of similar by-default footguns (off the top of my head: default 1-second precision, connection-local timezone autoconversion). At least for non-historic events.

Use uint64 as nanoseconds from epoch. Explicitly convert to/from UTC in your application code. Smear your leap seconds. This will last you until 2554, by which time I hope computers as we know are dead. Bonus: these make for decent strong-read-ordered internal UUIDs, given good retry logic.

I am looking at just this design pattern now (pseudo uuid) can you elaborate on “given good retry logic”?

I assume that on conflict just retry again, the timestamp will be different.

Reason #-2147483648 to use PostgreSQL instead of MySQL.

Reply on news.ycombinator.com