How to store timestamp with timezone

user_9197805 0
Viewed 78

I am trying to store a timestamp with timezone into my oracle db.

I have for example this code:

$deadline = new \DateTime('2018-11-07 13:33', new \DateTimeZone("EUROPE/BERLIN"));
$control->setDeadline($deadline);

and the result stored in my oracle db is this one: 07.11.18 13:33:00.000000000 +01:00

but my goal is to to store the timestamp with this format: 07.11.18 13:33:00.000000000 EUROPE/BERLIN

if I run this query the value is correctly saved with the desired format:

update my_table
set deadline = TIMESTAMP '2018-11-07 09:00:00 EUROPE/BERLIN'

What I am doing wrong? What is the correct way to format the timestamp to obtain the desired result with symfony?

symfony oracle
1 Answers

Looks like synfony transforms Europe/Berlin to +01:00 - which would be a bug.

As workaround you could do following:

ALTER SESSION SET TIME_ZONE = 'Europe/Berlin';

and then insert the value without any time zone, i.e. 

$deadline = new \DateTime('2018-11-07 13:33');
$control->setDeadline($deadline);

If you insert a timestamp into a TIMESTAMP WITH TIME ZONE column and you don't provide any time zone information then Oracle defaults the time zone to your current SESSIONTIMEZONE which you set before.

You can set your SESSIONTIMEZONE also by Environment Variable ORA_SDTZ or in your Registry at HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_{ORACLE_HOME Name}\ORA_SDTZ, resp. HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_{ORACLE_HOME Name}\ORA_SDTZ

user_30272660
Related