A Handy SQL Timestamp Function

Published: July 9, 2008

Hey, Ted:

I have found a great tool for working with timestamp data. I have used it for performance tracking and analyzing data intervals. It's SQL's Timestampdiff function, and I think you should tell readers of Four Hundred Guru about it.

Timestampdiff returns the difference between two timestamps in the interval of your choice. The first parameter for the timestampdiff function is the interval to use. Valid values for this parameter are:

The second parameter is used to subtract the two timestamp values. I cast the result as char(22).

Here's an example:

with STATISTICS as
  ( select count(*) as TRANSMITTED,
           min(TRANSMIT_TIMESTAMP) as START_TIMESTAMP,
           max(TRANSMIT_TIMESTAMP) as END_TIMESTAMP
      from XMITLOG
     where date(TRANSMIT_TIMESTAMP) = current_date)

select TRANSMITTED, START_TIMESTAMP, END_TIMESTAMP,
       timestampdiff(4,
          cast( (END_TIMESTAMP - START_TIMESTAMP) as char(22)))
          as ELAPSED_MINUTES
  from STATISTICS

The result set looked like this:

--Jeff

Thanks for bringing this powerful function to our attention, Jeff. One of the problems we've faced for so many years by storing dates and times in numeric and alpha fields is the difficulty of date/time arithmetic. It's marvelous to have such wonderful tools to work with these days.

--Ted