CODEWITHSUNDEEP
oracle-databasesymfony
Jon Doe
Jon Doe

Reputation: 469

How to store timestamp with timezone

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?

Upvotes: 2

Views: 124

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

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

Upvotes: 1

Related Questions