Re: SQL format string for converting epoch time?

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message news:7ooijv4jmbvbq7f096csuute74ikfquru8_at_4ax.com...
> On 12 Aug 2003 21:02:58 GMT, revjack <revjack_at_revjack.net> wrote:
>
> >Hello All,
> >
> >Is there a to_date() format string for converting perl's
> >'time' value to Oracle's DATE format?
> >
> >i.e. to_date('1060721626', '????????')
>
> there is no real need for that assuming the epoch starts on 1-1-70 and
> is a fraction of a day, you would simply
> to_date('01-jan-1970','dd-mon-yyyy') + <your epoch time converted to
> days>
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

A word of caution here. Its been quite a few years since I used Perl but assuming the internal Perl date you guys are speaking of is the UNIX style date (i.e. seconds since Jan 1, 1970 00:00:00 UTC) then if you use the above method you will need to adjust for the local timezone as the Oracle DATE (and also TIMESTAMP) data-types do not store a timezone attribute. If you use the above WITHOUT ajustment from UTC to local time you will get UTC which is probably not what you want [assuming you are using DATE (or TIMESTAMP without TZ)].

If you want to preserve the timezone and you are targeting 9i (i.e. will not work in Oracle 7 or 8) then you could consider converting directly to a TIMESTAMP WITH TIMEZONE data-type.

Cheers Received on Tue Aug 12 2003 - 17:41:32 CDT