News for hackers. Stuff that matters.
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...