MySQL Unix Timestamp: Clean Up Time

“Code is Poetry.”  I saw that graffiti’d in San Francisco recently (not surprising) and, after much deliberation, I think there is some truth to be found there.  When the boss of poetics, Sammy Coleridge, attempted to distinguish poetry from prose, he said, “Poetry: the best words in the best order.“  In this way, the finest programmers are, in fact, poets.  While capable of dizzying feats, they are always trying to streamline and simplify their language; they are conscious of aesthetics; of re-writing a piece of code just to eliminate a single comma; of “beautiful” language.  And, of course, the results can be spectacular.  Enter the Unix Timestamp function in MySQL.  You’ll need a lot more than a Unix Timestamp to become a poet (such as a course designed to help you master MySQL), but not only is this a great piece of code to have handy, it can eliminate a lot of ugly, unnecessary language—all while functioning brilliantly.

Unix Time is but the stream I go a-fishing in…

What is a Unix Timestamp?  Essentially, it’s a timescale, and is otherwise known as POSIX or Epoch time.  Unix time begins January 1, 1970 (more specifically, midnight of January 1, 1970 in UTC (Coordinated Universal Time, or the time at zero degrees longitude.  All the other time zones in the world are in relation to UTC.  More on this to come.).   Why?  Your guess is as good as mine . . . maybe to commemorate the death of the 60s?  Ultimately, the history of Unix time is not essential knowledge.  What is essential is the fact that a Unix Timestamp is one, long integer representing the number of seconds that have passed since 1/1/70.  You can use a timestamp by itself (indicating the number of seconds that have passed to the present moment) or in conjunction with a date (seconds that have passed up to the date given).  Here is what a Unix Timestamp might look like: 1285301069.

Or: 1 minute (60 seconds) * 1 hour (60 minutes) * 1 day (24 hours) * 1 year (365 days) = 31536000 seconds in one year.

Normally, large numbers aren’t considered an advantage.  But we’ve only just begun.

You may delay, but Unix Time will not…

Let’s look at the syntax.  To insert a standard Unix Timestamp, which will yield seconds since 1/1/70 to the present, use:

Don’t make it too complicated.  You may be tempted to write things like:

• UNIX_TIMESTAMP(UTC_TIMESTAMP())
• UNIX_TIMESTAMP(LOCALTIMESTAMP())

The first example will fail and result in your local time zone, and while the second will function properly, it’s unnecessarily long.  Be a poet and keep it trim.

For a Date argument:

Easy enough.  You can specify the date to the second.  For example:

That would give you seconds from 1/1/70 to the end of the world.  Unix Timestamp is obviously a humble argument, but scripters still commonly use more complex and less efficient systems, such as multiple variables for one date.  Instead of creating unnecessary variables (not in line with the poetics of code), you can replace them with a single integer.

The best uses for Unix Timestamps are in temporary scenarios.  You should also note that while Unix Timestamps may not be ideal for person-to-person communication, they are compatible with APIs from many programming languages, such as PHP (if you’ve always wanted to study MySQL and PHP in tandem, now you can).

If you do want to convert a Unix Timestamp into a humanly readable format, use either of these options for From Unixtime:

This returns a representation of the Unix Timestamp in one of the following formats: “YYYY-MM-DD HH:MM:SS” or “YYYYMMDDHHMMSS,” depending on how the function is used; string or numeric, respectively.  The value will subsequently be expressed in the current time zone, which reminds me…

In MySQL, the DateTime value will always be conveyed in the time zone in which it is written.  If you, i.e. your servers, are isolated to one time zone, you won’t run into any problems.  You can still use a Unix Timestamp to clean up, but you won’t be swapping multiple values for the same time (such as servers in New York and California).  However, Unix Timestamps will give you a consistent time zone in MySQL, which is subsequently useful for PHP, which itself bases many of its functions on Unix Timestamps.  In this way, a Unix Timestamp is used to represent every time zone in the world.  It converts your connection’s time zone to UTC.  As a general rule, it’s considered intelligent programming to express dates and times in UTC in any language.

Never waste Unix Time on people you don’t like…

Let’s look at a brief real world example using (or rather, utilizing) a Unix Timestamp in MySQL.  The whole idea behind Unix time is its consistency (speaking of which, learn how to consistently get the most out of your time).  Consider a table containing a column listing the times in which access is granted and denied.  You would use the From Unixtime statement to return a column of readable dates and times:

Again, you can imagine the advantage for international time zones.  If they are expressed in Unixtime, they will be generated in respectively logical terms when converted back to real time.  That is, real time will simply be a readable format of UTC.

So, that’s MySQL Unix Timestamp in a blogpost nutshell.  There are many, many uses for Unix time.  But as I mentioned, the more you know about MySQL and the principles of coding, the more functionality (and aesthetic beauty) you’ll be able to wring out of it.  My final suggestion is to take your artistic intentions into another realm with photo editing in MySQL.