Database Reference
In-Depth Information
TIME_TO_SEC(NOW()) / 60 /60 AS 'Hours';
+---------------------+--------------------+------------+
| NOW() | TIME_TO_SEC(NOW()) | Hours |
+---------------------+--------------------+------------+
| 2014-02-18 03:30:00 | 12600 | 3.50000000 |
+---------------------+--------------------+------------+
For the first field here, we're getting the current time. Notice that the time portion is ex-
actly 3:30 a.m. For the second field, we're using
TIME_TO_SEC()
to get the number of
seconds for that time: three and a half hours into the day. The third field is a calculation to
confirm that: 12,600 seconds equals 3.5 hours.
Conversely, if you know the number of seconds that have elapsed since the start of an
event — whether it be the start of a day or an action — you can usethe
SEC_TO_TIME()
function to give you a time. Suppose you have two events and you
want to know how much time elapsed between them. For instance, we might have a bird
identification test online. The user would be presented with an image of a bird and asked
to identify it. We would record the time when the image is displayed. When the user
enters the correct identification, that time is recorded in another column in the same table.
We could use
SEC_TO_TIME()
to get the difference between the two times, but in a
time format (i.e.,
hh:mm:ss
). Let's create an example of that by first creating a table to
record each bird-watcher's test results:
CREATE TABLE
bird_identification_tests
(
test_id
INT
AUTO_INCREMENT
KEY
,
human_id
INT
,
bird_id
INT
,
id_start
TIME
,
id_end
TIME
);
There's not much to this table: we just want to record the
human_id
for the member, the
bird_id
for the image presented to the member, and then the start and completion
times. We don't care about the date, just how long it took the member to identify the bird.
Let's insert some data into that table, just one row of data so that we'll be able to try the
SEC_TO_TIME()
function:
INSERT INTO
bird_identification_tests
VALUES
(
NULL
,
16
,
125
,
CURTIME
(),
NULL
);
Notice that we didn't provide a value for the
id_end
column. That will be set when the
member completes the identification. We're simulating this scenario, but if we were doing
this for a site, we would embed this
INSERT
statement in a script that's executed when
the user is shown a bird image. Another script containing an
UPDATE
statement would be
