Database Reference
In-Depth Information
| 2014-02-08 10:23:32 | 2014-02-08 | 10:23:32 |
+---------------------+------------+--------------+
All three of these functions and their synonyms use formats readable or easily understand-
able by humans. There are, however, built-in functions that return the Unix time, which is
the number of seconds since the “epoch” mentioned earlier. These can be useful when
comparing two temporal values. The following example shows the equivalent of
NOW()
asa
TIMESTAMP
:
SELECT UNIX_TIMESTAMP( ), NOW( );
+------------------+---------------------+
| UNIX_TIMESTAMP( ) | NOW( ) |
+------------------+---------------------+
| 1391874612 | 2014-02-08 10:50:12 |
+------------------+---------------------+
This returns the number of seconds since since January 1, 1970. Let's test that. Here's a
simple calculation to determine the number of years since the start of 1970, and a more
complicated way of determiningit:
SELECT (2014 - 1970) AS 'Simple',
UNIX_TIMESTAMP( ) AS 'Seconds since Epoch',
ROUND(UNIX_TIMESTAMP( ) / 60 / 60 / 24 / 365.25) AS 'Complicated';
+--------+---------------------+-------------+
| Simple | Seconds since Epoch | Complicated |
+--------+---------------------+-------------+
| 44 | 1391875289 | 44 |
+--------+---------------------+-------------+
This was run near the start of the year 2014 so we usedthe
ROUND()
function to round
down the number of years for a simple comparison. It's good to do exercises like this to
confirm and to better know functions like this one. It helps you to understand and trust
them.
Let's look at a more meaningful example in which you might want to use Unix time. Sup-
pose you want to know how many days ago our bird-watchers spotted a particular bird, a
Black Guineafowl
(
bird_id
309). To do this, we can use a join like so:
SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
ROUND((UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP(time_seen)) / 60 / 60 / 24)
AS 'Days Since Spotted'
FROM bird_sightings JOIN humans USING(human_id)
WHERE bird_id = 309;
