CODEWITHSUNDEEP
sqloracle-database
Leniaal
Leniaal

Reputation: 1777

Inserting timestamp with timezone in text in SQL

I created an insert statement that inserts the current time into a field with datatype:

TIMESTAMP(6) WITH TIME ZONE

insert into(
...
)
values(
9645
,0
,CAST(CURRENT_TIMESTAMP AS TIMESTAMP WITH TIME ZONE)
,user
,CAST(CURRENT_TIMESTAMP AS TIMESTAMP WITH TIME ZONE)
,0
,'APPROVED_P'
,1
,1
)

This works fine, but for one problem

enter image description here

I would like to have the timezone in text as well, but I'm unable to find a solution. Hope someone can help me out.

Upvotes: 3

Views: 12472

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59543

Time zone +01:00 is not the same as CET. Mayor difference is CET considers daylight saving times whereas +01:00 does not.

If you want time zone CET then you must set your session time zone accordingly with ALTER SESSION SET TIME_ZONE = 'CET'; or you set CURRENT_TIMESTAMP AT TIME ZONE 'CET' instead of CURRENT_TIMESTAMP

You should see the difference when you run this:

SELECT SESSIONTIMEZONE, 
    TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss tzh:tzm tzr tzd'),
    TO_CHAR((current_TIMESTAMP - INTERVAL '6' MONTH), 'yyyy-mm-dd hh24:mi:ss tzh:tzm tzr tzd')
FROM DUAL;

Upvotes: 5

Related Questions