CODEWITHSUNDEEP
sqloracle-databasedateformat
mysticfalls
mysticfalls

Reputation: 455

Date conversion in oracle

I have this part of query that is converting the value of create_date so it can be compared to the system date.

date_time has a data type of number.

NEW_TIME(TO_DATE('1970/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss') + (date_time/86400),
        'GMT','CDT')

Can someone explain to me the conversion that is happening? Why is it using the following values like 1970/01/01 00:00:00 and 86400?

Thank you.

Upvotes: 1

Views: 5321

Answers (3)

Michał Niklas
Michał Niklas

Reputation: 54292

Date '1970/01/01 00:00:00' is start date of unix "epoch". Many systems count seconds from this time. Number 86400 is number of seconds in one day. 86400 = 60 seconds * 60 minutes * 24 hours

Upvotes: 3

Mehmet Balioglu
Mehmet Balioglu

Reputation: 2302

TO_DATE('1970/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss')

The data in you column may be in string format 1970/01/01 00:00:00 and with to_date, you tell Oracle to convert it to date format as yyyy/mm/dd hh24:mi:ss It still shows as 1970/01/01 00:00:00 but it has a DATE datatype now. For more informaton about Oracle DATE datatype look here.

And 86400 is a day in terms of seconds: 60 seconds x 60 minutes x 24 hours = 86400

You use this to add a new day!

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

Whoever or whatever created the numeric value in date_time has created it as "seconds since 1st Jan 1970 00:00".

So, the values are based from 1970/01/01.

What you add 1 to a date, you're adding 1 day. So, the date_time value is being divided by the number of seconds in a day. (60s * 60m * 24h = 86400s)

When you add Date_Time / 86400 days to the base date, you get the datetime that the value represents

Upvotes: 0

Related Questions