Hi all,

I'm having troubles storing the date/time data in UTC format in DB2.
Database: DB2 9.7 on Windows 2003 Server. Timezone locale: Eastern Time with daylight savings.
The data is received in file batches in UTC format.

The problem is when I try to upload the UTC date/time into the TIMESTAMP column in DB2 it gets adjusted by DB2 automatically.

example:
VALUES ( TIMESTAMP('2010-03-14 02:15:00'));
result: 3/14/2010 3:15:00 AM

As you can see time gets adjusted by 1 hour since 2:00 am not a valid hour value for March 14, 2010. That's the point when daylight savings gets into effect.

Is there any way to enforce DB2 to use a column with TIMESTAMP datatype as is or/and in UTC format?

Any help will be greatly appreciated!

I'm not sure if you understand correctly what is happening. Example:

DB2 does not adjust column values automatically. What you are seeing could be a result of the difference in locale settings between the DB2 server and your client.

Timezone locale: Eastern Time with daylight savings

Lenny

So?

Thank you for your responses!

Both the server and the client have EST timezone with a checked checkbox for "automatically adjust clock for daylight saving changes".
Still confused on why the timestamp values are not displayed as they are stored in the database in certain cases...

I created table with TIMESTAMP column having few values between "2010-03-14 02:00:00" to "2010-03-14 02:59:00"

-- Table Code
/*
CREATE TABLE RSI.TEST_DATE_TIMESTAMP
(
REC_ID BIGINT NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 1
,INCREMENT BY 1
,NO MAXVALUE
,NO CYCLE)

,date_read DATE
,timestamp_read TIMESTAMP
,CONSTRAINT PK_TEST_DATE_TIMESTAMP_REC_ID PRIMARY KEY (REC_ID)
)
-- Table data
INSERT INTO RSI.TEST_DATE_TIMESTAMP
(
date_read
,timestamp_read
)
VALUES ( DATE('2010-03-14 02:15:00'), TIMESTAMP('2010-03-14 02:15:00') )
, ( DATE('2010-03-14 02:30:00'), TIMESTAMP('2010-03-14 02:30:00') )



-- Query
VALUES TIMESTAMP('2010-03-14 02:15:00')

*/


--************************************************** ********************************************
1) AQUA STUDIO IDE(3rd party client)
--************************************************** ********************************************
[TABLE]
command: SELECT * FROM RSI.TEST_DATE_TIMESTAMP ->
result:
1 3/14/2010 3/14/2010 3:15:00 AM
2 3/14/2010 3/14/2010 3:30:00 AM


[QUERY]
command: VALUES TIMESTAMP('2010-03-14 02:15:00') ->
result:
3/14/2010 3:15:00 AM


[EXPORT TABLE]
command: manual export of a table data to txt file using IDE
result:
"REC_ID","DATE_READ","TIMESTAMP_READ"
1,"2010-03-14","2010-03-14 03:15:00"
2,"2010-03-14","2010-03-14 03:30:00"



[EXPORT QUERY]
command: manual export of a query to txt file using IDE
result:
"1"
"2010-03-14 03:15:00"


--************************************************** ********************************************
2) DB2 CONTROL CENTER CLIENT TOOL
--************************************************** ********************************************
[TABLE]
command: SELECT * FROM RSI.TEST_DATE_TIMESTAMP ->
result:
1 3/14/2010 3/14/2010 3:15:00 AM
2 3/14/2010 3/14/2010 3:30:00 AM

[QUERY]
command: VALUES TIMESTAMP('2010-03-14 02:15:00') ->
resuls:
3/14/2010 3:15:00 AM


[EXPORT TABLE]
command: EXPORT TO "C:\1\electra_controlcenter_table.txt" OF DEL MESSAGES "C:\Program Files\IBM\SQLLIB\1" SELECT * FROM RSI.TEST_DATE_TIMESTAMP!
result:
1,20100314,"2010-03-14-02.15.00.000000"
2,20100314,"2010-03-14-02.30.00.000000"


[EXPORT QUERY]
command: EXPORT TO "C:\1\electra_controlcenter_query.txt" OF DEL MESSAGES "C:\Program Files\IBM\SQLLIB\1" SELECT TIMESTAMP('2010-03-14 02:15:00') FROM SYSIBM.SYSDUMMY1!
result:
"2010-03-14-02.15.00.000000"



--************************************************** ********************************************
3) DB2 COMMAND LINE TOOL
--************************************************** ********************************************
[TABLE]
db2 => SELECT * FROM RSI.TEST_DATE_TIMESTAMP

REC_ID DATE_READ TIMESTAMP_READ
-------------------- ---------- --------------------------
1 03/14/2010 2010-03-14-02.15.00.000000
2 03/14/2010 2010-03-14-02.30.00.000000

[QUERY]
db2 => VALUES TIMESTAMP('2010-03-14 02:15:00')
1
--------------------------
2010-03-14-02.15.00.000000

Not sure what you mean by "not displayed as they are stored in the database in certain cases".

DB2 always stores date, time, and timestamp the same way, regardless of the territory code of the server or what format it is inserted with (assuming that DB2 understands the format and can translate it correctly). They are stored similar to packed decimal (but without the sign-half byte at the end). This means they are stored with 2 digits per byte. For example, a data is always stored as YYYYMMDD in 4 bytes.

The way it is displayed (with dashes, commas, periods, etc) and the order of MONTH, DAY, YEAR, etc depends on the the territory code of your client and has nothing to do with the way it is stored in the database.

Since in both cases where the time shows incorrectly you use Java programs (Aqua Data Studio and DB2 Command Editor), my guess would be that your Java locale settings are incorrect or the JDK installation does not have the DST fixes applied. What is the DB2 client version?

Marcus_A, I meant that the displayed/exported timestamp values are off by one hour for the time periods when the daylight saving change takes into effect as indicated in the examples from my previous post.

n_i, thanks for the hints. I'll try to check settings for Java locale and/or find DST fixes.

DB2 client version as taken from CONTROL CENTER
================================================== ==========
About DB2 Administration Tools Environment
================================================== ==========
DB2 administration tools level:
Product identifier SQL09070
Level identifier 08010107
Level DB2 v9.7.0.441
Build level s090521
PTF NT3297
================================================== ==========
Java development kit (JDK):
Level IBM Corporation 1.6.0
================================================== ==========

AQUA STUDIO version is 8.0.13