select cast(current_date as timestamp(6))+(current_time - time '00:00:00' hour to second) as ts,current_time;
6/23/2015 00:18:16.000000 01:18:16
select cast(cast('2015-06-05' as date) as timestamp(6))+(time '10:00:00' - time '00:00:00' hour to second) as ts;
I am using TD 14.10. While coverting current_date and current_time as timestamp i am getting worng output. When i am converting hardcoded value or table values getting correct output.
is there anything worng in the above query? Please advise.
I am guessing time zone might be the reason for it. Can you try once with below SQL.
select cast(current_date as timestamp(6) AT LOCAL )+(current_time - time '00:00:00' hour to second) as ts,current_time
select cast(current_date as timestamp(6))+(current_time - time '00:00:00' hour to second) ts ,
current_time at local as local_time,current_time ;
ts local_time current_time
6/23/2015 04:12:28.000000 04:12:28 05:12:28
As you said, while doing arithmatic operations it takes local time. But when giving current_time, its giving diffeerent time. are the local time and server time same?
Hi, As per my understanding,
Local time --> local system time(PC/Lap/cloud)
Server time --> server time where the teradata is installed.
But when select current_time --> gives server time,
when add/sub something with current_time, it gives local system time.
Can anyone explain the difference please.
Client and server time not necessary to be same. The difference that you observed is due to timezone differences in server system and client machine
When we do SELECT CURRENT_DATE/TIME, It will try to cast DATE/TIME respective current session time zone, If timezone is not set in session it will try to cast with respective system time zone.
Thanks for the explaination....
But why current_time is giving different output.
current_time-time '00:00:00' hour to second : 04:12:28
when we use interval it works fine.
current_time - interval '00:00:00' hour to second : 05:12:28