How to convert CURRENT_TIMESTAMP into UTC timestamp?

Database

How to convert CURRENT_TIMESTAMP into UTC timestamp?

      CURRENT_TIMESTAMP is often used in the UPDATE SQL statement, such as "update tablename set start_timestamp=CURRENT_TIMESTAMP(0) where xxxxx".

      The timestamp got is the locatime of the Teradata server,  but i want to get the UTC timestamp and store UTC time in the field. How can i do that?

      Thanks,

4 REPLIES

Re: How to convert CURRENT_TIMESTAMP into UTC timestamp?

        There are some columns with Timestamp data type defined in database. Some are updated by UPDATE SQL statement with CURRENT_TIMESTAMP,   some are updated by JPA in Java. The problem is the java program may run remote in different TimeZone from Teradata Server, the java program fill  local timestamp where it run into database, but CURRENT_TIMESTAMP will get the curent local timestamp of Teradata Server,  so there are some unconsistency for the timestamp fields.

        I can update the Java source code to only write UTC timestamp into database, but how can i convert CURRENT_TIMESTAMP into UTC timestamp, in other words, how can i get the current UTC timestamp of  Teradata Server?  

       Could someone give some suggestion about this issue?

       Thanks

Junior Contributor

Re: How to convert CURRENT_TIMESTAMP into UTC timestamp?

Is the column defined as TIMESTAMP or TIMESTAMP WITH TIME ZONE?

try cast(current_timestamp as timestamp)

Dieter

Re: How to convert CURRENT_TIMESTAMP into UTC timestamp?

Hi Dieter,

       The column was defined as TIMESTAMP, in fact, i also tested TIMESTAMP WITH ZONE, the result is the same.

       In fact,  i tried SQL "select CURRENT_TIMESTAMP(0), cast(CURRENT_TIMESTAMP(0) as TIMESTAMP)", the two columns has the same result where the TimeZone isn't UTC.

Benjamin

Enthusiast

Re: How to convert CURRENT_TIMESTAMP into UTC timestamp?

Prior to TD 13.10, time and timestamp values are stored in the database based on system local time.  In 13.10, that behavior can be changed by setting the TimeDateWZControl system parameter via the DBSCONTROL utility.  To store datetime values as UTC, you will need to change this from the default setting.  See the utilities manual for details.   Even with UTC enabled, you will still get local time unless the data type is TIME with TIME ZONE or TIMESTAMP WITH TIME ZONE.

On my system,  when TimeDateWZControl is set to 2 (enable UTC), I get the following results:

select current_timestamp(0) at time zone 'gmt';

Current TimeStamp(0) AT TIME ZONE 'gmt'

---------------------------------------

              2011-09-27 19:26:50+00:00

select current_timestamp(0) at local;

 Current TimeStamp(0) AT LOCAL

-----------------------------

    2011-09-27 11:27:15-08:00

select cast(current_timestamp(0) at time zone 'gmt' as timestamp(0));

 Current TimeStamp(0) AT TIME ZONE 'gmt'

---------------------------------------

                    2011-09-27 11:27:42