Search code examples
databaseoracle-databasedate-formatting

Oracle: calculate day fraction of DATE type column

I have a DATE type column in Oracle table. As I understand it holds information about hours, minutes and seconds. I need to get a day Fraction of this column. Currently I am using this construction:

TO_CHAR(p.snaptime, 'HH24')/24 + TO_CHAR(p.snaptime, 'MI')/1440 + TO_CHAR(p.snaptime, 'SS')/86400

Is there more elegant (=shorter) way to get the same? Preferably even more time/CPU efficient for calculating it by Oracle. Thank you.

Solution

  • Hmmm. How about this?

    select (p.snaptime - trunc(p.snaptime))

    The difference of two dates is a number. In this case, the number will represent the time of the day. If you wanted this in some other unit, then you can convert by multiplication (* 24 for hours, * 24 * 60 for minutes, * 24 * 60 * 60 for seconds).