### 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

02/11/2011 02/12/2011 02/11/2011 3:35:02 02/11/2011 3:35:01

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

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
11 Feb 2011

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

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 -
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.

14 Feb 2011

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

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

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

16 Feb 2011

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

Thanks Dieter!

- Craig

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)

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

19 Jul 2011

Thank you!

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