Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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 the t_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');
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata