Performing Calculations with TIMESTAMP Values
Problem
You want to calculate intervals between
TIMESTAMP
values, search for records based on a
TIMESTAMP
column, and so forth.
Solution
TIMESTAMP
values are susceptible to the same kinds
of date calculations as DATETIME
values, such as
comparison, shifting, and component extraction.
Discussion
The following queries show some of the possible operations you can
perform on TIMESTAMP
values, using the
tsdemo2
table from Recipe 5.34:
Records that have not been modified since they were created:
SELECT * FROM tsdemo2 WHERE t_create = t_update;
Records modified within the last 12 hours:
SELECT * FROM tsdemo2 WHERE t_update >= DATE_SUB(NOW( ),INTERVAL 12 HOUR);
The difference between the creation and modification times (here expressed both in seconds and in hours):
SELECT t_create, t_update, UNIX_TIMESTAMP(t_update) - UNIX_TIMESTAMP(t_create) AS 'seconds', (UNIX_TIMESTAMP(t_update) - UNIX_TIMESTAMP(t_create))/(60 * 60) AS 'hours' FROM tsdemo2;
Records created from 1 PM to 4 PM:
SELECT * FROM tsdemo2 WHERE HOUR(t_create) BETWEEN 13 AND 16;
Or:
SELECT * FROM tsdemo2 WHERE DATE_FORMAT(t_create,'%H%i%s') BETWEEN '130000' AND '160000';
Or even by using
TIME_TO_SEC( )
to strip off the date part of thet_create
values:SELECT * FROM tsdemo2 WHERE TIME_TO_SEC(t_create) BETWEEN TIME_TO_SEC('13:00:00') AND TIME_TO_SEC('16:00:00');
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.