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.