date time during Daylight Savings

Database
Enthusiast

date time during Daylight Savings

SELECT
CAST( CAST(SUBSTR(CAST('20161106183050630' AS VARCHAR(50)),1,8) AS TIMESTAMP(3) FORMAT 'yyyymmdd') AS TIMESTAMP(3) )
+
(((CAST(CAST(CAST(CAST(SUBSTR(CAST('20161106183050630' AS VARCHAR(50)),9,6) ||'.' || SUBSTR(CAST('20161106183050630' AS VARCHAR(50)),15,3) AS FLOAT) AS FORMAT '99:99:99.999') AS CHAR(12)) AS TIME(3))) -
TIME '00:00:00.000') HOUR TO SECOND(3))

we have this above query where we convert a sting to timestamp 3 field. the query works correcly except for the 6 Nov 2016 date when the day light savings came in effect.. the expected result of this query is '2016-11-06 18:30:50.630' but what we get is 2016-11-06 17:30:50.630. and this only subtract one hour on the 6 nov 2016. if we supply any other date it works fine.
so the question is can w modify the sql so so it doess not do this implicitly time when Daylight savings take effect


Accepted Solutions
Teradata Employee

Re: date time during Daylight Savings

Yes, it's incomplete. Timestamp format doesn't allow the radix point for fractional seconds to be implied.

 

SELECT CAST(SUBSTR('20161106183050630',1, 14) || '.' ||SUBSTR('20161106183050630',15, 3) AS TIMESTAMP(3) FORMAT 'yyyymmddhhmiss.s(3)')

1 ACCEPTED SOLUTION
6 REPLIES
Teradata Employee

Re: date time during Daylight Savings

You are adding 18 hours 30 minutes and 50.63 seconds to midnight on 2016-11-06.  Sometime in the early morning (1 or 2 or 3 a.m.) of that day the clock rewound one hour.  The day has an extra hour in it, so the time at 18:30:50.63 after midnight really is 17:30:50.63.

 

Can't you just save a lot of trouble and cast the string as timestamp format 'yyyymmddhhmisss(3)'?  or maybe

CAST(SUBSTR('20161106183050630',1, 14) || '.' ||
CAST(SUBSTR('20161106183050630',15, 3) AS TIMESTAMP(3) FORMAT 'yyyymmddhhmiss.s(3)')

Enthusiast

Re: date time during Daylight Savings

i tried to run the sql but it gave an error.. is it incomplete. 

SyntaxEditor Code Snippet

SELECT CAST(SUBSTR('20161106193050630',15, 3) AS TIMESTAMP(3) FORMAT 'yyyymmddhhmiss.s(3)') Invalid Timestamp Error

for this specific query i am trying to ignore the  Daylight savings so basically i just want to add 18:30 hours to the day .. doesnt matter what timezone/DST it is . 

 

Teradata Employee

Re: date time during Daylight Savings

Well, this works, but it seems like there should be an even easier way to do it:

 

select cast(substr('20161106183053456',1,4) || '-' ||
    substr('20161106183053456',5,2) || '-' ||
    substr('20161106183053456',7,2) || ' ' ||
    substr('20161106183053456',9,2) || ':' ||
    substr('20161106183053456',11,2) || ':' ||
    substr('20161106183053456',13,2) || '.' ||
    substr('20161106183053456',15,3)
as timestamp(3) format'yyyy-mm-ddBhh:mi:ssds(3)');

Teradata Employee

Re: date time during Daylight Savings

Yes, it's incomplete. Timestamp format doesn't allow the radix point for fractional seconds to be implied.

 

SELECT CAST(SUBSTR('20161106183050630',1, 14) || '.' ||SUBSTR('20161106183050630',15, 3) AS TIMESTAMP(3) FORMAT 'yyyymmddhhmiss.s(3)')

Teradata Employee

Re: date time during Daylight Savings

We have a script which calculates the date to trigger a few jobs; we subtract interval '1' day from the given date for a few checks. However on 2nd October [daylight saving in Sydney] between 00:00 - 02:00 (before the daylight saving time i.e. 03:00), if we subtract 1 day interval it takes us 25 hours behind instead of 24.

 

SELECT CAST(SUBSTR('20171002000001001',1, 14) || '.' ||SUBSTR('20171002000001001',15, 3) AS TIMESTAMP(3) FORMAT 'yyyymmddhhmiss.s(3)') - INTERVAL '1' DAY;

Expected Answer: 01/10/2017 00:00:01.000000

Answer we get: 30/09/2017 23:00:01.000000

 

Is there any proper workaround or am I missing anything here.

Teradata Employee

Re: date time during Daylight Savings

I believe the problem is that CAST from character to timestamp without time zone assumes the current (pre-adjustment) session time zone offset, i.e. +10:00.

You probably need to use TIMESTAMP WITH TIME ZONE instead.