Ben Cantrick (mackys) wrote,
Ben Cantrick
mackys

  • Music:

FYI: How to insert a GMT date time into an Oracle database date field.


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.
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 0 comments