How to add time with hour in TD 14.10?

General
Enthusiast

How to add time with hour in TD 14.10?

select cast(current_date as timestamp(6))+(current_time - time '00:00:00' hour to second) as ts,current_time;

Output:
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;

Output:
TS
6/5/2015 10:00:00.000000

Hi All,

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.

5 REPLIES
Enthusiast

Re: How to add time with hour in TD 14.10?

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

Enthusiast

Re: How to add time with hour in TD 14.10?

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

Thanks Anil,

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?

Enthusiast

Re: How to add time with hour in TD 14.10?

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.

Thanks,

Enthusiast

Re: How to add time with hour in TD 14.10?

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.

Enthusiast

Re: How to add time with hour in TD 14.10?

Thanks for the explaination....

But why current_time is giving different output.

CURRENT_TIME: :05:12:28

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

Thanks,