Home » SQL & PL/SQL » SQL & PL/SQL » need help with converting unix timestamp column
icon5.gif  need help with converting unix timestamp column [message #205411] Fri, 24 November 2006 10:20 Go to next message
nycjoelb
Messages: 4
Registered: November 2006
Junior Member
Hi everyone,

I have a simple select querry but need to convert my unix timestamp to output as date and time. I've tried to_char but does not work. Can anyone help?

Below is my output:

SQL> select equipment, offering_desc, charge_amount,timestamp from purchase
2 where OFFERING_DESC='NEW Silent Hill'
3 order by timestamp asc;

EQUIPMENT OFFERING_DESC CHARGE_AMOUNT TIMESTAMP
----------------- -------------------------- ------------- ----------
000013770416 NEW Silent Hill 395 974496991
000013770416 NEW Silent Hill 395 974734188
000011051c3a NEW Silent Hill 395 1161282865
000011051c38 NEW Silent Hill 395 1161282865
000013770f84 NEW Silent Hill 395 1163177745
Re: need help with converting unix timestamp column [message #205416 is a reply to message #205411] Fri, 24 November 2006 11:03 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select to_char(to_date('01-JAN-1970 00:00:00', 'DD-MON-RRRR HH24:MI:SS') + (974496991)/(3600*24),
	'DD-MON-RRRR HH24:MI:SS') "Time" from dual;

Time
--------------------------------
17-NOV-2000 21:36:31
Re: need help with converting unix timestamp column [message #205419 is a reply to message #205416] Fri, 24 November 2006 11:19 Go to previous messageGo to next message
nycjoelb
Messages: 4
Registered: November 2006
Junior Member
Thank you veery much, this works 4 me.
Re: need help with converting unix timestamp column [message #205425 is a reply to message #205416] Fri, 24 November 2006 12:19 Go to previous messageGo to next message
nycjoelb
Messages: 4
Registered: November 2006
Junior Member
SQL> select to_char(to_date('01-JAN-1970 00:00:00', 'DD-MON-RRRR HH24:MI:SS') + (974496991)/(3600*24),
'DD-MON-RRRR HH24:MI:SS') "Time" from dual;


IfI needed to compensate for a timezone, where di I put the -5:00?

Should the statement read below?

SQL> select to_char(to_date('01-JAN-1970 00:00:00', 'DD-MON-RRRR HH24:MI:SS -5:00') + (974496991)/(3600*24),
'DD-MON-RRRR HH24:MI:SS') "Time" from dual;
Re: need help with converting unix timestamp column [message #205430 is a reply to message #205425] Fri, 24 November 2006 13:16 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
To account for a 5 hour time difference, 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: grants
Next Topic: Can Use Connect statement into PL/SQL?
Goto Forum:
  


Current Time: Fri Dec 09 04:12:56 CST 2016

Total time taken to generate the page: 0.14245 seconds