Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Get Timestamp from Milliseconds (since 1970) - incl. Timezone, Daylight Saving

1041351Nov 18 2013 — edited Nov 20 2013

Hello,

unfortunately I either find threads where users ask to get milliseconds for a given date (wrong direction for me) or they are implementin in java (I am on SQL).

In one table therese a column which stores a specific date as milliseconds since 1970. In my view I want to convert this to a time-type (timestamp).

On Datetime Datatypes and Time Zone Support I found to use

TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'

But I dont know which date to assing because of how to calculate correct time (summer time or not?).

One ugly but probably solution could be to use

TIMESTAMP '1970-01-01 00:00:00 EU/Germany GMT'

and then add the amount of hours resulting of the milliseconds since then hoping Oracle will challenge time zone and daylight saving and leap year on its own.

But there must be a better solution, not?

Thanks for any tips.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 18 2013
Added on Nov 18 2013
14 comments
11,087 views