Year 2038 problem: TIMESTAMP type instead of DATETIME

[div3r]

Hi there, we're using propel as ORM on Spryker platform.

We're experiencing issues while using the Timestampable Behavior because dates are more higher than year 2037 and now migrations break because it doesn't know how to convert those dates.

And also timestamps are only valid until 2037 (because of 32 bit), this is also bad decision because of long term support.

[dereuromark]

As per release notes:

As another side effect timestamps are only valid until 2037 (32bit). Make sure to adjust any databuilders or fixtures accordingly.

What is the need for timestamps beyond that date? Sure, long term we might have to talk about this after 2030 - but until then I am sure there is a different ORM system alltogether that took over :)

[wollanup]

Hey, not using beta2 yet, but this change may be a dealbreaker in some cases.

We use external services to archive legal documents, and we store the date until document have to be retained, we may have dates between 10 ans 20 years. 2038 is "only" 15 years far from now :)

Can you tell us the main benefits of TIMESTAMP vs. DATETIME ? I saw TIMESTAMPS handles timezone, uses less spaces, query caching and indexing, DATETIME doesn't.

Maybe ther are other concerns with other SQL engines ? (Didn't check the commit...)

Is it not possible to keep both types in XML schema ?

(And as usual congrats to mainteners and contributors !)

[dereuromark]

Usually, datetime is any date both past and future and should be used here. timestamp is a stamp of that moment in time, and can be used for storing the current time Thus the 32bit limit should be fine for usage of this.

The "less space" argument might be nano optimization. I am not sure how much impact modern DBs here actually have. Would be nice if someone could make this a proven argument to show that there is indeed value in using timestamp for datetime purposes

@mringler what do you think?

[mringler]

Yes, it is exactly as you say, @dereuromark .

Basically, MySQL (and MariaDB) offers both types, with some small but notable differences between them, as you already listed @wollanup. Particularly auto-initialization of timestamps in MariaDB has repeatedly dumbfounded me, where the first timestamp column in a table will have default value "now". In general, timestamp is well-suited if you want to record a specific event time, most notably creation or modification time of a row, and it will usually be "now" at write time, while datetime is preferred if you want to store a date as a literal, like with the mentioned legal documents. You might want to check discussion in https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql for a more detailed picture.

The problem in Propel was that it only knew the type "TIMESTAMP", which was translated to datetime in MySQL, and the MySQL Timestamp type or the DATETIME keyword could not be used at all. Which is unnecessary limiting, led to problems when working with databases created outside Propel, and overall is just weirdly confusing.

With the latest beta, you can use both TIMESTAMP and DATETIME in schema.xml, and they will give you the expected type in MySQL. Which I think makes sense and improves the situation overall, but for now leaves us with an admittedly annoying change.

But the very simple solution is to go over the TIMESTAMP columns in your schema.xml and decide if they really are timestamps, or if you want to keep them as DATETIME columns, in which case you need to change type to that.

For other DBMSs, which usually only have the DATETIME type, nothing changes, except that the column type can now be written as datetime for more clarity.

[dereuromark]

As this is in the official release notes and seems to be in line with the expectations generally, I dont think there is much more we can do here. Lets close it then for now.

[wollanup]

With the latest beta, you can use both TIMESTAMP and DATETIME in schema.xml

Oh well, this is perfect, I misunderstood that part when reading the release notes ! Thank you @mringler for clarification.