[2.x] Change all timestamps in the database to use DATETIME instead of INT or BIGINT

[swampopus]

This is in stark defiance of one of my original issues, #6931 :)

I've changed my mind. BIGINT helps us out in 2038, but if I can dream, it would be to use DATETIME for human readability.

I often look through tables directly, or look at values via dpm() or a node's devel tab, and I frequently have to copy and paste the created or changed values into unixtimestamp.com to get a conversion. It would be so nice to just see a human-readable date and time.

Pros

• Easier for humans to read the date in any context (db, web, mysqldump, etc)
• Queries are semantically easier to follow. Ex: SELECT * WHERE posted IS BETWEEN (2026-01-01 AND 2026-12-31)
• Easily handled by strtotime() if you really want to get it back to an int.

Cons

• In PHP, it can save a little bit of typing & CPU clock cycles if you add date() + 7200, for example, to mean "2 hours from now".
• I'm sure there's a bunch of code that assumes the database timestamps are going to be ints, and a lot of contrib modules that similarly would need to be updated. It would be a bunch of work, no doubt.
• To convert to a prettier format than Y-m-d, you'd need to do something like: format_date('pretty', strtotime($dt)); It's a little more wordy.

Discussion

I am curious if anyone out there knows of a good reason to store dates and times as integers. I know Drupal does it, and several other projects as well. Is it just a holdover from a previous era, or are there really benefits to only using ints?

[indigoxela]

So, in which time zone should those dates get stored? 😜

Integers are much easier to calculate with and are always UTC - no pitfalls or ambiguity with time zones.
And this change would be a super-breaker. No way to be backwards compatible in any way. For ... dates nicer to read in devel? Sorry, I don't think that justifies such a breaking change, which will require rewrites of the majority of contrib and custom code. Even in a major release that's rather a no-go.

You see, I'm a fan of timestamps. I see no way someone could convince me to use datetime instead. 😉

[swampopus]

You're right about timezones-- they are in fact the bane of my existence as a programmer. And I agree they should always be UTC.

But-- and you're going to hate this-- you could easily have a convenience function to use instead of REQUEST_TIME or time(), which gives you a DATETIME instead of an INT. Ex:

update node...... changed = ?, [backdrop_utc_datetime()]

In this example, backdrop_utc_datetime() always returns the current UTC datetime as a Y-m-d H:i:s formatted string. It would be kinda like a drop-in replacement for date() or REQUEST_TIME

And yeah, it would definitely break backwards compatibility, but for more than just reading dates in devel, but for all the other examples I gave.

I don't know if this is a vote of confidence or not, but for what its worth, Wordpress stores dates as DATETIME, as do other open source projects. I don't think I'm going to convince you :) but that's why I wanted to open this issue. I'm curious what others think about it.

[argiepiano]

I love this kind of conversations and debates!

[EDITED - removed line since I misread part of your message]

I'm also not sure this is worth the troubles, but hey, it's 2.0, everything is possible!

Currently Backdrop stores Field API date fields (unless otherwise chosen) as DATETIME. The timezone thing is always a nightmare. Timezone for those fields are not stored in the database - you have to get the handling from the configuration json file through the tz_handling and timezone_db properties. The conversion can be different depending on whether your site uses the user's TZ or the site's. Also, dates are stored differently when you have a granularity of only Y-m-d (always stored with 00:00:00, and in the site's tz to prevent adjusting to the previous or next day), or if you include h:m:s in their granularity (typically stored as UTC, making the readability argument completely moot and more confusing than a Unix timestamp, given that someone who hasn't inspected the json file doesn't know what scheme is being used). Hey, at least with Unix timestamp you know it's universal!

While Backdrop provides functions to make these conversions easy (e.g. format_date) this becomes a real nightmare when querying the database with db_select or db_query and using conditions, for example. Storing unix makes it more predictable and easier to query. I mean, take a look at the kinds of juggles Views has to do with this: db_tz_support(), views_date_sql_field() and all the lines of code to manage this craziness in date views handler. Also, when moving between dev to production, my dev mysql server is not set up to support IANA timezone codes (yes, I'm too lazy to investigate how to change this). So, I'm forced to manually remove the flag date_db_tz_support from the state table so that Views with Field API dates with Y-m-d granularity continue working as expected.

I'm not saying this is not possible, but I'd love for this to be uniform across schema fields, entity properties and Field API fields.

[avpaderno]

Handling relative times with timestamps is easier than handling relative times with DATETIME, which (on PHP side) would require converting a timestamp to a string.

On 64-bit PHP builds, time() already returns 64-bit integers. It is only a matter of storing all the 64 bits for database rows like created.

[avpaderno]

For storing dates/times after January 1, 1970, 64-bit timestamps are sufficient. For dates before January 1, 1970, I would use a DATETIME database row.
I am not sure Backdrop core needs a row for dates/times that are before January 1, 1970.

[swampopus]

Well, though it hurts my argument, a signed BIGINT can store a date going back to 292,277,022,657 BC (or, 280ish billion years OLDER than the age of the universe). A DATETIME can only store as early as the year 1000.

EDIT: Of course, this would leave us open to the Y292B bug eventually 😎

[avpaderno]

At least date() seems able to handle negative timestamps (echo date("Y-m-d H:m:s", -366), "\n";). I just hope Backdrop core handle timestamps like the following code. 😉

// Remove temporary files that are older than BACKDROP_MAXIMUM_TEMP_FILE_AGE.
// Use separate placeholders for the status to avoid a bug in some versions
// of PHP. See http://drupal.org/node/352956.
$result = db_query('SELECT fid FROM {file_managed} WHERE status <> :permanent AND timestamp < :timestamp', array(
  ':permanent' => FILE_STATUS_PERMANENT,
  ':timestamp' => REQUEST_TIME - BACKDROP_MAXIMUM_TEMP_FILE_AGE
));
foreach ($result as $row) {
  if ($file = file_load($row->fid)) {
    $references = file_usage_list($file);
    if (empty($references)) {
      $file->delete();
    }
    else {
      watchdog('file system', 'Did not delete temporary file "%path" during garbage collection because it is in use by the following modules: %modules.', array('%path' => $file->uri, '%modules' => implode(', ', array_keys($references))), WATCHDOG_INFO);
    }
  }
}

[swampopus]

At the moment in the database, the core tables use signed INTs, so dates can't go past 2038, or be older than 1901.