TIMESTAMPDIFF
-TIMESTAMPDIFF--(--numeric-expression--,--string-expression--)-><
The TIMESTAMPDIFF function returns an estimated number of intervals of
the type defined by the first argument, based on the difference between
two timestamps.
numeric-expression
The first argument must be a built-in data type of either INTEGER or
SMALLINT. Valid values of interval (the first argument) are:
1 Fractions of a second
2 Seconds
4 Minutes
8 Hours
16 Days
32 Weeks
64 Months
128 Quarters
256 Years
string-expression
string-expression is the result of subtracting two timestamps and
converting the result to a string of length 22. The argument must be an
expression that returns a value of a built-in character string or a
graphic string.
If string-expression is a character or graphic string, it must not be a
CLOB or DBCLOB.
The result of the function is an integer. If either argument can be
null, the result can be null; if either argument is null, the result is
the null value.
The following assumptions may be used in estimating the difference:
there are 365 days in a year
there are 30 days in a month
there are 24 hours in a day
there are 60 minutes in an hour
there are 60 seconds in a minute
These assumptions are used when converting the information in the second
argument, which is a timestamp duration, to the interval type specified
in the first argument. The returned estimate may vary by a number of
days. For example, if the number of days (interval 16) is requested for
a difference in timestamps for '1997-03-01-00.00.00' and
'1997-02-01-00.00.00', the result is 30. This is because the difference
between the timestamps is 1 month so the assumption of 30 days in a
month applies.
Example
Estimate the age of employees in months.
SELECT
TIMESTAMPDIFF(64,
CAST(CURRENT_TIMESTAMP-CAST(BIRTHDATE AS TIMESTAMP) AS CHAR(22)))
AS AGE_IN_MONTHS
FROM EMPLOYEE
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Friday, April 23, 2010 4:06 PM
To: midrange-l@xxxxxxxxxxxx
Subject: How to calculate the difference between 2 timestamp fields in
SQL (theanswer in seconds)
Afternoon everyone
I have 2 timestamp fields.
In SQL, how do I calculate the difference between them, in seconds
I tried the following
timestampA - timestampB
and the answer was one field
for example
2010-04-23-17.04.06.222000 - 2010-04-23-14.00.01.042000
and the result was 30,405.180000
Now I can see that is rally 3 hours, 4 minutes 5.18 seconds
But being Friday afternoon, ONLY 16 24 ounce cups of coffee - well my
brain
is a little frazzled
I'm just hoping someone knows the answer to my question because they've
done it before
As always
any responses are MUCH appreciated
thanks in advance
Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
midrange.com
