How to convert a Greenwich Mean Time to an ordinary date/time in sql? 1

[cyan01]

Hi, Experts,

I have an Oracle table which has a field whose datetype is NUMBER. The values in this field are the outputs of a perl library function "time", i.e. the number of non-leap seconds since 01/01/1970, UTC (Greenwich Mean Time). What is the sql syntax to convert this value into an ordinary date/time format?

Thanks!

 

[lewisp]

If I understand your requirements correctly, you need something like this

SELECT To_Date('01/01/1970','DD/MM/YYYY') + (<your number>/86400)
FROM sys.dual;

This is the number of days since 01/01/1970 added to 01/01/1970 to give you the current date. The number of days is calculated as your number divided by the number of seconds in a day.

The result will give you the date in UTC, so you will need to adjust for your timezone.