?

Log in

No account? Create an account
FYI: How to insert a GMT date time into an Oracle database date field. - Adventures in Engineering — LiveJournal
The wanderings of a modern ronin.

Ben Cantrick
  Date: 2007-09-10 15:05
  Subject:   FYI: How to insert a GMT date time into an Oracle database date field.
Public
  Music:Soundgarden - Head Injury

Google-bait: "how to insert a GMT (aka UTC) date (datetime) into an Oracle database date field"

I spent half a day hitting my head on the desk over this, I hope someone else will be spared the pain...

INSERT INTO mytable (varchar, date)
VALUES ('foo', SYS_EXTRACT_UTC(SYSTIMESTAMP))


You would think this would be an easy thing, but evidently it's quite hard. Google searches yielded nothing useful. There were several solutions to this problem posted in various places that involved some variation on NEW_TIME(SYSDATE, 'OLDTZ', 'GMT'). But those are useless when you don't know what OLDTZ is.

The problems are basically twofold: A) the timezone can be set (or not) on a per DB session basis, and B) the DB itself has its own timezone separate from that, which is what SYSDATE is given in. When you're inserting a GMT date/time, you probably neither know nor care what TZ the DB server is in. The whole point of UTC, after all, is that it's the same no matter where you are on the planet.

If you were (futiley) pursuing the NEW_TIME() approach, you might try and get the server TZ by doing EXTRACT(TIMEZONE_ABBR FROM SYSDATE). But in my case, that returned "UNK" - as if the DB server somehow doesn't know what timezone it lives in!

I think the right thing here is just for the DB server to provide a SYSTIME_UTC. Or to just return SYSTIME in UTC if the session doesn't explicitly set a timezone.
Post A Comment | | Link






browse
May 2015