Posts Tagged ‘timestamp’

ISO 8601 and MySQL

I was curious what to name a variable holding a string with a date formatted for MySQL. Something like “startDateMysql” seemed silly, so I went digging as to what MySQL’s date format was based on.

The YYYY-MM-DD format used by MySQL is defined by ISO 8601.

The HH:MM:SS format is also defined by ISO 8601, but MySQL never adds the time zone designator, a ‘Z’ indicator for UTC or an offset from UTC. Without the time zone designator, the time is implied to be local time according to the standard, but MySQL returns non-local time without a designator, which is non-conformant.

When combining a date and time, MySQL omits the ‘T’ delimiter, which is allowed and permitted by “mutual agreement.”

Generating a random timestamp in MySQL

Random timestamps are fairly simple to generate and useful when creating test data or fake data for demos. The technique involves converting a MySQL timestamp into unix time, adding or subtracting a certain time range (in seconds), then converting back to a MySQL timestamp.

I’ve used this technique presented on before, where a starting timestamp is specified and a random time span is added to the timestamp. However, I wanted to try something slightly different and, in my opinion, a bit more robust. Instead of specifying a starting timestamp, I wanted a pivot timestamp from which a random time span is randomly added or subtracted.

SET @pivot_ts = '2013-01-15 22:15:45';
SET @max_span = 432000; /* 5 days in seconds */
SET @bias = SIGN(-0.5 + RAND());

UNIX_TIMESTAMP(@pivot_ts) + ( @bias * (FLOOR(RAND()*@max_span)) )

Of course all the variables can be made part of the SELECT statement to make everything more succient.