TIMESTAMP

UDA
Highlighted
Enthusiast

TIMESTAMP

I have a stored procedure that sets a variable as follows:
SELECT CAST(CURRENT_TIME(2) AS TIMESTAMP(2))

However, it has returned '2007-09-26 09:12:12.01' when it should have returned '2007-09-27 09:12:12.01'.
The only element of the date being incorrect is the day of month.

Is this something we know about in Teradata?
14 REPLIES
Enthusiast

Re: TIMESTAMP

A couple of question that might help:

1) The CURRENT_TIME value is obtained on the Teradata server. Is the clock of that server(s) properly set?

2) Are you working with Teradata for Unix or Windows? Are there some different time zone than yours set at Operational System level?

Good luck!
Enthusiast

Re: TIMESTAMP


1.) Depends on your definition of properly set. Timezone is set as GMT - Dublin, Edinburgh etc with
automatic adjustments for Daylight Savings. We are in Ireland. The Actual time is synched with
timeserver time.windows.com

2.) Underlying OS on our Teradata Servers is Windows Server 2003.
My PC would be synched with Domain at logon, not sure what master time server is used for this.

Remembering that the consistent issue is with the day of month element only have we anymore ideas?
Teradata Employee

Re: TIMESTAMP

Did you try CURRENT_TIMESTAMP as oppose to CURRENT_TIME?

Are CURRENT_TIMESTAMP and CURRENT_DATE also off by 1?
Enthusiast

Re: TIMESTAMP

When I run the below statements in Queryman the 1st gives me a wrong date and the 2nd one is correct. When my colleagues do the same they get 2 correct dates.
When I run them in BTEQ they are both correct.
I'm not sure if this causes the underlying issue as it clouds the issue further.
I am going to reboot & have coffee just to be sure.

SELECT CAST(CURRENT_TIME(2) AS TIMESTAMP(2)); -- 2007-09-29 15:47:36.94
SELECT CAST(CURRENT_TIMESTAMP(2) AS TIMESTAMP(2)); -- 2007-10-03 15:47:37.06
Enthusiast

Re: TIMESTAMP

Hi Supreme Being,

What is the difference between the CURRENT_TIME and CURRENT_TIMESTAMP ? is it only in the output format ?

Regards
Tenacity88.
Enthusiast

Re: TIMESTAMP

Hi Marcmc,

My appologies, i mistook the title " Supreme Being " as your name in my previous post. Hope you would pardon me :-).

regards
Tenacity88.
Teradata Employee

Re: TIMESTAMP

Conceptually there are two differnt algorithms for converting "TIME WITH ZONE" (CURRENT_TIME) to "TIMESTAMP".

a) Convert "TIME WITH ZONE" to "TIME" and then add current the System Date to it to
create "TIMESTAMP".

b) Convert "TIME WITH ZONE" to "TIMESTAMP WITH ZONE" and then convert to "TIMESTAMP".

I think Teradata uses a.

This is the result on my system using BTEQ

SET TIME ZONE INTERVAL'11:00' Hour To Minute;

*** Set SESSION accepted.
*** Total elapsed time was 1 second.

select cast(TIME'23:00:00+00:00' as TIMESTAMP);

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

23:00:00+00:00
--------------------------
2007-10-03 10:00:00.000000


select cast(TIMESTAMP'2007-10-03 23:00:00+00:00' as TIMESTAMP);

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

2007-10-03 23:00:00+00:00
--------------------------
2007-10-04 10:00:00.000000

Note that the dates are off by 1.

I suggest CURRENT_TIMESTAMP.
Enthusiast

Re: TIMESTAMP

There is a related known problem, DR 114070:

"CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP do not return the correct value for the default session time zone. Instead, they returned the year, month, day, hour, and minute having the same value no matter what the session time zone is. Therefore, CURRENT_TIME and CURRENT_TIMESTAMP are only correct when the session time zone is equal to the time zone specified in DBSControl."

If this problem impacts your application, contact the Teradata Support Center for fix availability info.
Enthusiast

Re: TIMESTAMP

thanks all, i appreciate all your efforts.