News for hackers. Stuff that matters.
Re:64-bit unix timestamp is not supported in MySQL functions... by PeterZaitsev on Tuesday December 10, 2019 @08:07PM (#21758570)
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
Re:64-bit unix timestamp is not supported in MySQL functions... by q3k on Tuesday December 10, 2019 @08:19PM (#21758632)
MySQL has more unfortunately-named time-related types than Python, which IMO is quite the achievement:

   - TIME
   - DATE
   - YEAR
   - TIMESTAMP
   - DATETIME
Re:64-bit unix timestamp is not supported in MySQL functions... by lathiat on Tuesday December 10, 2019 @08:34PM (#21758723)
decades of history and following decades old standards from the start of computing in the 70s, 80s.. will do that to you.
Re:64-bit unix timestamp is not supported in MySQL functions... by jbverschoor on Wednesday December 11, 2019 @03:44AM (#21760686)
Decades of MySQL not following standards you mean?
Re:64-bit unix timestamp is not supported in MySQL functions... by petre on Wednesday December 11, 2019 @02:42AM (#21760425)
They should probably map to DATETIME internally but it inefficient for storage.
Re:64-bit unix timestamp is not supported in MySQL functions... by gruez on Tuesday December 10, 2019 @09:55PM (#21759157)
>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.

Re:64-bit unix timestamp is not supported in MySQL functions... by Macha on Tuesday December 10, 2019 @10:06PM (#21759197)
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.
Re:64-bit unix timestamp is not supported in MySQL functions... by msla on Tuesday December 10, 2019 @10:30PM (#21759303)
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.

Re:64-bit unix timestamp is not supported in MySQL functions... by Macha on Tuesday December 10, 2019 @11:40PM (#21759646)
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.

Re:64-bit unix timestamp is not supported in MySQL functions... by msla on Wednesday December 11, 2019 @01:31PM (#21765321)
> 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.

Re:64-bit unix timestamp is not supported in MySQL functions... by raverbashing on Wednesday December 11, 2019 @02:54AM (#21760473)
But you can have TZ aware and unaware datetimes in Python. It's not the best API but it's there
Re:64-bit unix timestamp is not supported in MySQL functions... by PhasmaFelis on Tuesday December 10, 2019 @09:54PM (#21759151)
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.

Re:64-bit unix timestamp is not supported in MySQL functions... by blattimwind on Tuesday December 10, 2019 @10:14PM (#21759234)
First time I've seen someone complain about finding standard ISO 8601 in a database.
Re:64-bit unix timestamp is not supported in MySQL functions... by PhasmaFelis on Tuesday December 10, 2019 @11:36PM (#21759632)
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.
Re:64-bit unix timestamp is not supported in MySQL functions... by mjbmitch on Tuesday December 10, 2019 @10:12PM (#21759224)
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.
Re:64-bit unix timestamp is not supported in MySQL functions... by PhasmaFelis on Tuesday December 10, 2019 @11:41PM (#21759653)
> 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.

Re:64-bit unix timestamp is not supported in MySQL functions... by selimthegrim on Tuesday December 10, 2019 @10:00PM (#21759175)
Maybe they worked for NATO in a previous job?
Re:64-bit unix timestamp is not supported in MySQL functions... by PaulHoule on Tuesday December 10, 2019 @09:32PM (#21759045)
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.
Re:64-bit unix timestamp is not supported in MySQL functions... by paulryanrogers on Tuesday December 10, 2019 @11:26PM (#21759592)
None of which are time zone aware. Potsgresql on the other hand...
Re:64-bit unix timestamp is not supported in MySQL functions... by CaliforniaKarl on Tuesday December 10, 2019 @08:15PM (#21758618)
Is this the only part of MySQL/MariaDB that is known to have a Year-2038 issue?
Re:64-bit unix timestamp is not supported in MySQL functions... by tyingq on Tuesday December 10, 2019 @08:44PM (#21758788)
Yes. UNIX_TIMESTAMP(), FROM_UNIXTIME(), and the TIMESTAMP datatype. Everything else time related appears to be fine.
Re:64-bit unix timestamp is not supported in MySQL functions... by horizone on Tuesday December 10, 2019 @08:24PM (#21758673)
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.
Re:64-bit unix timestamp is not supported in MySQL functions... by jolmg on Tuesday December 10, 2019 @06:50PM (#21758108)
Apparently, it's also an issue with MariaDB.
Re:64-bit unix timestamp is not supported in MySQL functions... by OrgNet on Tuesday December 10, 2019 @08:13PM (#21758602)
I don't know, but I also don't know why anyone didnt make the switch to MariaDB yet...
Re:64-bit unix timestamp is not supported in MySQL functions... by PeterZaitsev on Tuesday December 10, 2019 @08:26PM (#21758679)
Ignorance is bliss ?

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

Re:64-bit unix timestamp is not supported in MySQL functions... by tyingq on Tuesday December 10, 2019 @08:50PM (#21758830)
Re:64-bit unix timestamp is not supported in MySQL functions... by PeterZaitsev on Tuesday December 10, 2019 @09:12PM (#21758947)
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.

Re:64-bit unix timestamp is not supported in MySQL functions... by Izkata on Tuesday December 10, 2019 @11:55PM (#21759702)
Fear of breaking stuff. We still have some MySQL 5.1
Re:64-bit unix timestamp is not supported in MySQL functions... by devwastaken on Tuesday December 10, 2019 @09:16PM (#21758961)
Performance most likely.
Re:64-bit unix timestamp is not supported in MySQL functions... by mackal on Tuesday December 10, 2019 @08:32PM (#21758717)
Ran into this issue on a project I work on :P
Re:64-bit unix timestamp is not supported in MySQL functions... by bigtunacan on Tuesday December 10, 2019 @08:07PM (#21758572)
This is going to be great for programmer job security in 2036/2037 if we just keep it hush hush for awhile longer.
Re:64-bit unix timestamp is not supported in MySQL functions... by web007 on Tuesday December 10, 2019 @08:29PM (#21758700)
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.
Re:64-bit unix timestamp is not supported in MySQL functions... by tapland on Tuesday December 10, 2019 @08:24PM (#21758670)
We just added in some new TIMESTAMPs today. Gotta make sure the code is maintainable in the future you know.
Re:64-bit unix timestamp is not supported in MySQL functions... by oarabbus_ on Tuesday December 10, 2019 @08:00PM (#21758529)
Reason 34,583,498 to use Postgres instead of MySQL.
Re:64-bit unix timestamp is not supported in MySQL functions... by q3k on Tuesday December 10, 2019 @08:07PM (#21758568)
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.

Re:64-bit unix timestamp is not supported in MySQL functions... by jpeg_hero on Tuesday December 10, 2019 @10:32PM (#21759312)
I am looking at just this design pattern now (pseudo uuid) can you elaborate on “given good retry logic”?
Re:64-bit unix timestamp is not supported in MySQL functions... by thecopy on Wednesday December 11, 2019 @04:07AM (#21760792)
I assume that on conflict just retry again, the timestamp will be different.
Re:64-bit unix timestamp is not supported in MySQL functions... by nwallin on Tuesday December 10, 2019 @09:15PM (#21758954)
Reason #-2147483648 to use PostgreSQL instead of MySQL.