OraFAQ Forum: Unix » Convert Unix time

Home » Infrastructure » Unix » Convert Unix time

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Convert Unix time [message #25709]

Wed, 14 May 2003 09:40

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

Report message to a moderator

Re: Convert Unix time [message #25710 is a reply to message #25709]

Wed, 14 May 2003 09:50

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

Report message to a moderator

Re: Convert Unix time [message #256829 is a reply to message #25710]

Mon, 06 August 2007 18:11

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

Report message to a moderator

Re: Convert Unix time [message #256837 is a reply to message #25709]

Mon, 06 August 2007 20:58

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.

Report message to a moderator

Re: Convert Unix time [message #256912 is a reply to message #25709]

Tue, 07 August 2007 02:42

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

Report message to a moderator

Re: Convert Unix time [message #256928 is a reply to message #256912]

Tue, 07 August 2007 03:12

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Convert Unix time [message #256952 is a reply to message #256912]

Tue, 07 August 2007 04:46

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;

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 09:11:10 CST 2024