Using INTERVAL to Add or Subtract Fractional Seconds to or from TIMESTAMP Values

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Fan

Problem: How do you add or subtract a "fractional second" to a TIMESTAMP column in Teradata?

The available documentation is not very clear, so let's look into it using examples.

Basic use of INTERVAL

Build, Populate, and Query Test Table

CREATE VOLATILE TABLE t (
DateCol date,
Timestamp0 timestamp(0),
Timestamp3 timestamp(3),
Timestamp6 timestamp(6)
) ON COMMIT PRESERVE ROWS;

INSERT INTO t
VALUES (
CURRENT_DATE,
CURRENT_TIMESTAMP(0),
CURRENT_TIMESTAMP(3),
CURRENT_TIMESTAMP(6)
);

SELECT * FROM t;

Results

DateColTimestamp0Timestamp3Timestamp6
02/11/201102/11/2011 3:35:0202/11/2011 3:35:02.98002/11/2011 3:35:02.980000

Add a Day, Subtract a Second

SELECT
DateCol,
-- Simplest case: Add 1 day to a date
DateCol + INTERVAL '1' DAY AS AddOneDay,
Timestamp0,
-- Simplest case: Subtract 1 second from a timestamp(0)
Timestamp0 - INTERVAL '1' SECOND AS SubtractOneSecond
FROM t;

Results

DateColAddOneDayTimestamp0SubtractOneSecond
02/11/201102/12/201102/11/2011 3:35:0202/11/2011 3:35:01

Sub-Precision Addition and Subtraction

This is an interesting test. What happens when you add a millisecond to a timestamp(0) column? Or subtract?

Query

SELECT
Timestamp0,
-- Subtract 0.001 second from a timestamp(0)
Timestamp0 - INTERVAL '0.001' SECOND AS SubtractMillisecondFromTS0,
-- Add 0.001 second to a timestamp(0)
Timestamp0 + INTERVAL '0.001' SECOND AS AddMillisecondToTS0
FROM t;

Results

Timestamp0SubtractMillisecondFromTS0AddMillisecondToTS0
02/11/2011 3:35:0202/11/2011 3:35:0102/11/2011 3:35:02

It is interesting to see that subtracting decrements but adding does not increment. I found that this is consistent across multiple tests.

Matching Precision: Subtracting a Millisecond from a TIMESTAMP(3)

Query

SELECT
Timestamp3,
Timestamp3 - INTERVAL '0.001' SECOND AS SubtractMillisecondFromTS3
FROM t;

Results

Timestamp3SubtractMillisecondFromTS3
02/11/2011 3:35:02.98002/11/2011 3:35:02.979

Matching Precision: Subtracting a Microsecond from a TIMESTAMP(6)

Query

SELECT
Timestamp6,
Timestamp6 - INTERVAL '0.000001' SECOND AS SubtractMicrosecondFromTS6
FROM t;

Results

Timestamp6SubtractMicrosecondFromTS6
02/11/2011 3:35:02.98000002/11/2011 3:35:02.979999
10 Comments
Enthusiast
so.. another undocumented feature ??? LOL..
quote: "It is interesting to see that subtracting decrements but adding does not increment. I found that this is consistent across multiple tests."

I think what is happening here is that, internally, the addition/subtraction occurs at maximum precision, then the conversion of result back to TIMESTAMP(0) cuts off the fractional seconds. So subtracting any sub-second magnitude from n.000000 would take you to n-1, and addition of a sub-second magnitude keeps you at n.

I'm basing this off Teradata 13.10 manual: "Data Types and Literals", page 134-135 -
- http://www.info.teradata.com/edownload.cfm?itemid=102320042
TIMESTAMP is stored as a 10-byte field, where its SECOND field is internally stored in a four-byte field, with the last three bytes used for the fractional seconds portion. There must be careful logic internally to make sure that the fractional seconds portion gets zero'd out on storage, based on the field's precision.

Fan
crowley, thanks for the explanation (speculation?). Makes sense to me.
Yep, to clarify, my comments are speculation, but seem to fit the symptoms :)

Also, while on the topic of Teradata TIMESTAMP... i recently had to write the following to convert a Unix timestamp field to a Teradata TIMESTAMP field. I thought I'd post here, since the solution leverages INTERVAL to avoid a LOT of heavy date calculation lifting.

The issue we were trying to solve is that two raw tables (effectively grandiose log-files) had time fields that we needed to use to stitch events together. The time fields were in different formats, and I couldn't find an easier way in teradata to convert Unix timestamp to Teradata TIMESTAMP. Here's the code, if it is interesting. I wonder if the reverse (i.e. TD TIMESTAMP -> Unix timestamp) would be easier, but I haven't put thought into that.

At a high level, the code does the following (works for GMT timestamps):
- Create a table of reference Unix timestamp (beginning of 2011 in the below), and decides which to use
- Find out how many seconds occur after the reference timestamp
- Compute the number of days, hours, minutes and seconds after the reference timestamp
- Compute the fractional portion of the original timestamp (if any)
- Format the integral data to have fixed-length, zero-fill where appropriate
- Build a text timestamp from these components
- Cast the text timestamp to a TIMESTAMP(6) supplying the appropriate FORMAT clause

SELECT

/*
* Test cases
*
* 1325895367 == Sat, 07 Jan 2012 00:16:07 GMT
* 1295895367 == Mon, 24 Jan 2011 18:56:07 GMT
* 1263895367 == Tue, 19 Jan 2010 10:02:47 GMT
* 1231895367 == Wed, 14 Jan 2009 01:09:27 GMT
*/
CAST( '1325895367.123456' AS DECIMAL(16,6) ) AS unix_ts
--CAST( '1295895367.123456' AS DECIMAL(16,6) ) AS unix_ts
--CAST( '1263895367.123456' AS DECIMAL(16,6) ) AS unix_ts
--CAST( '1231895367.123456' AS DECIMAL(16,6) ) AS unix_ts

/* Constants used by date calculations */
,CAST( '1325376000' AS DECIMAL(16,6) ) AS unix_ts_2012
,CAST( '1293840000' AS DECIMAL(16,6) ) AS unix_ts_2011
,CAST( '1262304000' AS DECIMAL(16,6) ) AS unix_ts_2010
,CAST( '1230768000' AS DECIMAL(16,6) ) AS unix_ts_2009

,CASE
WHEN unix_ts >= unix_ts_2012 THEN unix_ts_2012
WHEN unix_ts >= unix_ts_2011 THEN unix_ts_2011
WHEN unix_ts >= unix_ts_2010 THEN unix_ts_2010
WHEN unix_ts >= unix_ts_2009 THEN unix_ts_2009
ELSE CAST( '0' AS DECIMAL(16,6) )
END
AS unix_ts_curr_year

,CASE
WHEN unix_ts >= unix_ts_2012 THEN (DATE '2012-01-01')
WHEN unix_ts >= unix_ts_2011 THEN (DATE '2011-01-01')
WHEN unix_ts >= unix_ts_2010 THEN (DATE '2010-01-01')
WHEN unix_ts >= unix_ts_2009 THEN (DATE '2009-01-01')
ELSE DATE
END
AS td_date_curr_year

/*
* Get the whole and fractional portions of the unix timestamp field
* Special cases: no seperator exists, zero-fill the fraction
*/
,( POSITION( '.' IN unix_ts ) ) AS index_seperator
,CAST (
CASE
WHEN index_seperator = 0 THEN 0
ELSE SUBSTRING( unix_ts FROM index_seperator+1 )
END
AS INTEGER FORMAT'9(6)' )
AS fraction_unix_ts

/*
* Compute integer values we need to construct a timestamp
* Teradata INTERVAL datatypes can only have a length of 4,
* so we have to get to days in the current year to have values
* that are always length 4 or less (actually length 3)
*/
,CAST ( (unix_ts - unix_ts_curr_year) AS INTEGER) AS seconds_curr_year
,( seconds_curr_year / 86400 ) AS days_curr_year
,( seconds_curr_year MOD 86400 ) AS total_seconds_left
,( total_seconds_left MOD 3600 ) AS hours_seconds_left

/*
* Compute HH, MM, SS and force them to be zero-filled with format statements
* Note: the "display" won't show the zeroes by default, but they are there
*/
,CAST ( ( total_seconds_left / 3600 ) AS INTEGER FORMAT '99' ) AS hours
,CAST ( ( hours_seconds_left / 60 ) AS INTEGER FORMAT '99' ) AS minutes
,CAST ( ( hours_seconds_left MOD 60 ) AS INTEGER FORMAT '99') AS seconds

/*
* The motivation for doing all of this is so we can use TD's interval datatype
* to do all the heavy lifting (i.e. date addition, which is a lengthy process)
*/
,CAST( days_curr_year AS INTERVAL DAY(4) ) AS interval_day_curr_year
,(td_date_curr_year + interval_day_curr_year) AS td_date

/* Construct the text timestamp by concatentating all our fields together */
,(CAST( td_date AS CHAR(10)) || ' ' || hours || ':' || minutes || ':' || seconds || '.' || fraction_unix_ts ) AS text_timestamp

/* aaaand Voila! Here we have a timestamp converted from a decimal(16,6) */
,CAST( text_timestamp AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SSDS(6)' ) AS td_ts

Junior Contributor
Hi Rob/crowley,
this truncation (based on applying a FLOOR to the result of a TimeStamp/Interval calculationis) is required according to Standard SQL, so Teradata is just following SQL:2008 rules.

Btw, Unix Timestamp to TD Timestamp and back:

,CAST(DATE '1970-01-01' + (unix_ts / 86400) AS TIMESTAMP(6))
+ ((unix_ts MOD 86400) * INTERVAL '00:00:01.000000' HOUR TO SECOND) AS td_ts

,(CAST(td_ts AS DATE) - DATE '1970-01-01') * 86400
+ (EXTRACT(HOUR FROM td_ts) * 3600)
+ (EXTRACT(MINUTE FROM td_ts) * 60)
+ (EXTRACT(SECOND FROM td_ts)) AS back_to_unix

Of course this only works as long as you don't use an implementation of a Unix Timestamp with leap seconds, then you better switch to a UDF :-)

Dieter
Wow! That's like flying over the grand canyon when all your friends are building bridges :)

Thanks Dieter!

- Craig
Junior Supporter
For changing a date timestamp to Unix Epoch, what am I missing here?

SELECT (CAST('2011-07-19 02:18:37' AS DATE) - DATE '1970-01-01') * 86400
+ (EXTRACT(HOUR FROM '2011-07-19 02:18:37') * 3600)
+ (EXTRACT(MINUTE FROM '2011-07-19 02:18:37') * 60)
+ (EXTRACT(SECOND FROM '2011-07-19 02:18:37')) AS back_to_unix

Error: A character string failed conversion to a numeric value. (State:22003, Native Code: FFFFF231)
Junior Contributor
Teradata doesn't know that '2011-07-19 02:18:37' is supposed to be a timestamp and as a date it's not valid.

SELECT (CAST(TIMESTAMP '2011-07-19 02:18:37' AS DATE) - DATE '1970-01-01') * 86400
+ (EXTRACT(HOUR FROM TIMESTAMP '2011-07-19 02:18:37') * 3600)
+ (EXTRACT(MINUTE FROM TIMESTAMP '2011-07-19 02:18:37') * 60)
+ (EXTRACT(SECOND FROM TIMESTAMP '2011-07-19 02:18:37')) AS back_to_unix

or
SELECT TIMESTAMP '2011-07-19 02:18:37' AS td_ts,
,(CAST(td_ts AS DATE) - DATE '1970-01-01') * 86400
+ (EXTRACT(HOUR FROM td_ts) * 3600)
+ (EXTRACT(MINUTE FROM td_ts) * 60)
+ (EXTRACT(SECOND FROM td_ts)) AS back_to_unix

Dieter
Junior Supporter
Thank you!
Enthusiast

For those getting the error Failed [2616 : 22003] Numeric overflow occurred during computation. like I was, you need to cast the day+hour+minute portion to decimal first:

SELECT CURRENT_TIMESTAMP AS t,
CAST((CAST(t AS DATE) - DATE '1970-01-01') * 86400
+ (EXTRACT(HOUR FROM t) * 3600)
+ (EXTRACT(MINUTE FROM t) * 60) AS DECIMAL(18))
+ (EXTRACT(SECOND FROM t)) AS back_to_unix