Valid from, valid until.

Who else has this irrational feeling that in some edge case, it might be not long enough?

Just wait till 2038. It's the new Y2K.

@atheist only if you're still using an archaic 32 bit Linux system?

@kwilliams "banks have definitely upgraded from cobol by now!"

@kwilliams you're right, most people have upgraded, but embedded systems, ATMs, banking infrastructure, the stuff that was built to never need to be changed? Some of that is still going. It's the same as Y2K. I think it'll be less of an issue, because there are other pressures to upgrade, but I think there will be a few last minute rushes to patch systems.

computers will change in several thousand years definitely

@atheist 2037 is the year I resign from anywhere that's running 32bit clocks

@C0D4 and anywhere that's using a bank that might have a 32bit clock! 💵

@jAsE-cAsE lol 6

@jAsE-cAsE hi. You seem to be in a strange phAsE, jAsE

Super annoying that Date types in programming languages & databases do not have some INF limit.

We highly suspect entropy is irreversible and there will be a heat death moment of the universe, but there's no absolute proof of that yet. The death of the universe also doesn't necessarily mean that time is finite.

It annoys me even more than MySQL says "0000-00-00 00:00:00" == Empty date.

I mean, what if "The second coming of Jesus Christ" should be interpreted in a dirty way... so first thing he does is create an onlyfans account, which then interprets his birth date as empty/null.

@bittersweet something something big bang, big collapse, repeat, possible alternative, physics degree, yada yada.

But yes, time is likely to be infinite.

But, mr. @atheist, explain to me, how should we account for the second cumming of Jesus-fucking-Christ, as a MySQL DB admin?

*installs postgres*

Oh it actually handles "+infinity" and "July 1st, 1500 BC", I stand corrected.

*throws MySQL server out of the window*

@bittersweet lol, you think I know anything about databases.

Most of my data is video. I can tell you how codecs work. H.264 has a cross built into it. Or is it an 8 pointed star?

@atheist Haha my job title says "Senior DBA" and I've been working with MySQL & Postgres for about 15 years now.

And just today I found out that Postgres can handle Infinity & BC dates.

Senior is such a bullshit title prefix 😂

@bittersweet I've literally never written a line of sql for my job. No ORM stuff. No nosql either. I've done like, some basic tutorial stuff on sql. Literally that's it.

Think I need a "Senior DBA" title too.

🤔

@atheist

Sure thing.

I hereby grant you the title of Senior Database Administrator, knight in the order of the Holy Primary Key.

Good luck with it. Don't come to me with your insertion deadlocks though, I'm drunk most of the time.

@bittersweet 🥳

@bittersweet instead of "infinite" I’d rather have something that represents "big enough to use for limits where the value doesn’t really matter".
The difference is of course semantic. IMO, infinity is not a proper representation for a date where something ends (validity in this case).

Swift has the value distantPast and distantFuture for DateTime objects (Date actually). I like that.

Just a reminder that there exist tons of applications where devs thought they were totally super clever and did timestamp based arithmetic...

2038 is gonna be interesting for sure.

@bittersweet depends on SQL server configuration.

5.7+

https://dev.mysql.com/doc/refman/...

< 5.7

https://dev.mysql.com/doc/refman/...

0000-00-00 00:00:00 should be NULL because of ODBC:
https://dev.mysql.com/doc/refman/...

The optimal datetime type would support dates from the start of the universe till its end...
...twice, because measurements and estimates always come with an error margin.

@Oktokolo that wouldn’t work. There is no clear definition of the end of the universe. It will probably slowly progress into the heat death but there won’t be a point in time where it will be ‘completed’.

And then there is the problem with relativity. Time is not universal.

Even if we could assign a time to the end of the universe, it would be absurdly huge. Without losing precision at huge values, like floating numbers do, it would be just wasted memory.

@Lensflare
Hmm, lets first define arbitraryRational as a pair of BCMath numbers where the first is the rational significand and the second is the integral exponent (the base is 10).
The actual number is significand times (10 to the power of exponent).

Then an arbitraryRationalInterval is a pair of arbitraryRational where the first is the start of the interval and the second is the non-negative length.

A datetime would then be an arbitraryRationalInterval where the 0 is equivalent to some arbitrarily chosen point in the Anthropocene and the minimum and maximum values would be parameters of the type on allocation.

Dates near our own epoch are still easy and efficient to define with subsecond precision while for dates in the far past no exact measurements are known...

There is nothing we can do to account for relativity in such a primitive type.
Location of the measurement and movement of it relative to other locations are way beyond the scope.

@Oktokolo not sure if I understand you correctly but I think you are suggesting something similar to floating point numbers for dates. In that case, the distant future would be unprecise (not definable within seconds).
We could choose to not care but then again, if we don’t care than we might as well simply define some special value for distant future.

I just can’t see a way to have a date type that can represent any point in time with the same precision that we need for the dates that represent ‘now’.

@Lensflare
I suggest arbitrary precision floats of base 10 for datetimes - two of them, so you can specify an arbitrarily precise interval containing the actual point in time.

The caveat is: When parsing input or defining persistent storage, just defining min and max will not cut it anymore.
You also have to decide about the maximum amount of digits that the two arbitrary precision floats may contain in their significand and exponent - similar as for SQL decimal types (but probably just as a single count for all digits).