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

DateCol Timestamp0 Timestamp3 Timestamp6
02/11/2011 02/11/2011 3:35:02 02/11/2011 3:35:02.980 02/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

DateCol AddOneDay Timestamp0 SubtractOneSecond
02/11/2011 02/12/2011 02/11/2011 3:35:02 02/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

Timestamp0 SubtractMillisecondFromTS0 AddMillisecondToTS0
02/11/2011 3:35:02 02/11/2011 3:35:01 02/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

Timestamp3 SubtractMillisecondFromTS3
02/11/2011 3:35:02.980 02/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

Timestamp6 SubtractMicrosecondFromTS6
02/11/2011 3:35:02.980000 02/11/2011 3:35:02.979999
Discussion
emilwu 34 comments Joined 12/07
11 Feb 2011

so.. another undocumented feature ??? LOL..

crowley 3 comments Joined 02/10
12 Feb 2011

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.

RobG 5 comments Joined 09/10
14 Feb 2011

crowley, thanks for the explanation (speculation?). Makes sense to me.

crowley 3 comments Joined 02/10
15 Feb 2011

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

dnoeth 86 comments Joined 11/04
15 Feb 2011

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

Dieter

crowley 3 comments Joined 02/10
16 Feb 2011

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

Thanks Dieter!

- Craig

teradatatester 2 comments Joined 01/10
19 Jul 2011

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)

dnoeth 86 comments Joined 11/04
19 Jul 2011

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

Dieter

teradatatester 2 comments Joined 01/10
19 Jul 2011

Thank you!

dlabar 5 comments Joined 06/11
12 Mar 2013

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

You must sign in to leave a comment.