Wednesday, January 16, 2013

Database dates

Before I started using Drupal, I recorded timestamps as MySQL timestamps, where were easily readable, but that's where the advantages end. Drupal opened my eyes to using the epoch value, which allows easy sorting, comparison and other fun math functions. Further, MySQL and whatever coding language you're using has support for converting epochs to human-readable strings in all kinds of formats.

It isn't without its own disadvantages, though. It's hard to read when you're mired in the MySQL console, though I imagine at some point, I'll know that 1400000000 is May 2014 (guess I know that now) and perhaps it'll become like looking at Matrix code.

Anyway, MySQL has various integer types and when you're designing your database table, you could have a field be either int, int unsigned, bigint or bigint unsigned.

First, it makes no sense to use the default signed unless you're dealing with dates before (January 1,) 1970.

Second, we may be looking at another Y2K problem, depending on how the date fields are setup.

Data Type Max # "Y2K" deadline
int
2147483647
Tue, 19 Jan 2038 03:14:07 GMT
int unsigned
4294967295
Sun, 07 Feb 2106 06:28:15 GMT
bigint
9223372036854775807
Sometime (waaaay) after 2286
bigint unsigned
18446744073709551615
Sometime (waaaaaay) after 2286

It looks like Drupal 6's default setup is a signed int, so I'll have to upgrade it sometime before 2038.

Update: I figured out the bigint Y2K dates... 292 billion years from now for the signed version, so I think whatever code you write will be long gone by then.