Performing Calculations with TIMESTAMP Values

5.35.1 Problem

You want to calculate intervals between TIMESTAMP values, search for records based on a TIMESTAMP column, and so forth.

5.35.2 Solution

TIMESTAMP values are susceptible to the same kinds of date calculations as DATETIME values, such as comparison, shifting, and component extraction.

5.35.3 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;


    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');

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: