0

I have my datetime column in table defined as Oracle NUMBER data type which contains data in this format 1363709957 for example.

I want to get these numbers in query so i can execute my insert scripts which will put in current time in there. What is the oracle SQL for that?

Sripaul
  • 2,227
  • 9
  • 36
  • 60
  • @Ben I do not want any conversion here. I just want the epoch time in oracle sql in NUMBER format. – Sripaul Mar 26 '13 at 09:28

1 Answers1

2

Your time stamp appears to be a standard Unix time stamp. You need to use arithmetic to convert this here's a post on OTN about this,

and one for the other direction.

Peter Wooster
  • 6,009
  • 2
  • 27
  • 39
  • I am using this SELECT (SYSDATE - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 FROM DUAL. But then it gives me values as 1364264318.999999999999999999999999999998 and not as whole number – Sripaul Mar 26 '13 at 09:18
  • 1
    This is normal floating point behavior. You can use the ROUND() function to make an integer of it. The INTEGER() function will truncate, so you need to round. – Peter Wooster Mar 26 '13 at 10:58