Home » Infrastructure » Unix » Convert Unix time
Convert Unix time [message #25709] Wed, 14 May 2003 09:40 Go to next message
CM
Messages: 2
Registered: May 2003
Junior Member
For the following column in table 'ABC', that represents a Unix time stamp (number of seconds since 1/1/1970):

TSTAMP NUMBER(11)

I need to construct a select statement that returns the date and time.

For example:
971782979
would return:
11-17-2000 07:42:59
Re: Convert Unix time [message #25710 is a reply to message #25709] Wed, 14 May 2003 09:50 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
CM, are you sure your math is correct?
SQL> CREATE OR REPLACE FUNCTION convert_unix_time (
  2      p_secs_since_epoch  IN  NUMBER
  3  )
  4  RETURN DATE
  5  IS
  6      l_date                  DATE;
  7  BEGIN
  8      l_date := TO_DATE('19700101','YYYYMMDD')
  9                +
 10                p_secs_since_epoch / 60 / 60 / 24;
 11      RETURN (l_date);              
 12  END convert_unix_time;
 13  /
  
Function created. 
  
SQL> SELECT TO_CHAR(convert_unix_time(971782979)
  2  ,              'MM-DD-YYYY HH24:MI:SS') my_time
  3  FROM   DUAL
  4  /
  
MY_TIME
-------------------
10-17-2000 11:42:59
  
SQL> 
Good luck,

A
Re: Convert Unix time [message #256829 is a reply to message #25710] Mon, 06 August 2007 18:11 Go to previous messageGo to next message
munisw1
Messages: 4
Registered: August 2006
Location: Union City, California
Junior Member
The function work fine for me. How about the time zone? How can i include the time zone consideration while converting the time?

thanks
Re: Convert Unix time [message #256837 is a reply to message #25709] Mon, 06 August 2007 20:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>How can i include the time zone consideration while converting the time?
Add or subtract the timezone offset number of seconds to/from results.
Re: Convert Unix time [message #256912 is a reply to message #25709] Tue, 07 August 2007 02:42 Go to previous messageGo to next message
munisw1
Messages: 4
Registered: August 2006
Location: Union City, California
Junior Member
The way I ran the query for each id

is

SQL> SELECT TO_CHAR(convert_unix_time(create_time)
2 , 'MM-DD-YYYY HH24:MI:SS') my_time
3 FROM user_auth where user_id=<id>.
4 /

What would be my syntax. I tried subtracting -5, but get SQL error.

thanks
Re: Convert Unix time [message #256928 is a reply to message #256912] Tue, 07 August 2007 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I understand you get I error if you subtract 5 from a string.
Isn't it obvious for you?

Regards
Michel
Re: Convert Unix time [message #256952 is a reply to message #256912] Tue, 07 August 2007 04:46 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
As a simple SQL command, you could use:

SQL> select to_char(to_date('01-JAN-1970 00:00:00', 'DD-MON-RRRR HH24:MI:SS -5:00') + (974496991)/(3600*24) - 5/24,
'DD-MON-RRRR HH24:MI:SS') "Time" from dual;

Previous Topic: oracle 9.2 Installation Error On HP-UX B.11.11
Next Topic: Need a shell script for sql query with into clause
Goto Forum:
  


Current Time: Fri Mar 29 01:34:04 CDT 2024