Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Monday December 04, 2006
12:02 PM

Oracle gem of the day: UTC timestamp

[ #31801 ]

I want to mark every record in the table with a TIMESTAMP WITH TIME ZONE value in UTC time. SYSDATE, of course, doesn't do timezones. SYSTIMESTAMP uses the server timezone, which is not UTC. CURRENT_TIMESTAMP uses the client timezone, which is not UTC. I could hardcode all my jobs to set $ENV{TZ} = 'UTC', but that seems fragile: somebody else could come along and wonder why in the world it's like that, or do something weird. Somebody could insert into the table through a program that failed to configure the TZ. I don't want to come back to a table with 3 or more different timezones in it, if I can help it, though since I'm using TIMESTAMP WITH TIME ZONE it would be fixable.

SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) is almost exactly what I need. It takes the timestamp and turns it into UTC. Unfortunately rather than returning a TIMESTAMP WITH TIME ZONE with the time zone set to UTC, it returns a plain TIMESTAMP, no timezone. So in the end, I go into the actual statement used to insert into the table (which if I'm smart is hidden in a package somewhere, or something) and set it to use FROM_TZ(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP), '00:00'). Wow. Seems like they could've just provided a function to do this, but at least it's possible.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.