How to convert a Greenwich Mean Time to an ordinary date/time in sql? 1

[cyan01]

Hi, Experts,

I have an Oracle table which has a field whose datetype is NUMBER. The values in this field are the outputs of a perl library function "time", i.e. the number of non-leap seconds since 01/01/1970, UTC (Greenwich Mean Time). What is the sql syntax to convert this value into an ordinary date/time format?

Thanks!

 

[mrdenny]

Check out this FAQ I wrote. It should do the trick for you.
faq183-5563

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[cyan01]

Thank you, Denny!

The syntax you used need to be modified a little in order to run in oracle. For example, a from clause is required, i.e. "from dual".

However, oracle does not take "dateadd". Could you please tell me what the corresponding oracle command is?

Thanks again!

 

[r937]

you do realize you posted in the ANSI SQL forum, right?

select to_date('1970/01/01','yyyy/mm/dd')
+ yourperltimecolumn/86400
from yourtable



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[cyan01]

Thank you, Rudy! Yes, I know I posted it in ansi-sql forum. I thought Oracle would endorse all ansi-commands.

Thank you again!

 

[r937]

i'd be surprised, but maybe you can test it for us and report back --

select date'1970-01-01'
+ interval yourperltimecolumn/86400 second
from yourtable


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[swampBoogie]

That's not how interval works

select date'1970-01-01'
      + cast(yourperltimecolumn as interval second(9))
  from yourtable

 

[cyan01]

To Rudy,

Were you asking me to confirm whether Oracle endorse all ansi sql commands? If I understand your request correctly, well, my answer is not very sure.

Please refer to the first following post to my original one by Denny. His solution is :

select dateadd(dd, (1101859200/86400), '1/1/1970')

It seems to me that 'dateadd' is an ansi sql command, right? But oracle 8i does not like it. Following are my tests:

SQL>select dateadd(dd, (1101859200/86400), '1/1/1970');
select dateadd(dd, (1101859200/86400), '1/1/1970')
                                                 *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL>select dateadd(dd, (1101859200/86400), '1/1/1970') from dual;
select dateadd(dd, (1101859200/86400), '1/1/1970') from dual
       *
ERROR at line 1:
ORA-00904: invalid column name

In addition, I also tested both your query and swampBoogie's on oracle 8i. Here are the outputs:

  1  select date'1970-01-01' + interval 1101859200/86400 second
  2* from dual
SQL>/
select date'1970-01-01' + interval 1101859200/86400 second
                                   *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

  1  select date'1970-01-01' + cast(1101859200 as interval second(9))
  2* from dual
SQL>/
select date'1970-01-01' + cast(1101859200 as interval second(9))
                                             *
ERROR at line 1:
ORA-00902: invalid datatype

BTW, I have to admit that I don't quite understand your sql statements. I hope that I have fulfiled your requests. And thank you all very much for your great help!

 

[r937]

well, it looks like you have discovered that oracle does not support all ansi sql

and no, dateadd is not ansi sql either



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[mrdenny]

dateadd is a T/SQL command, so it's not going to be supported by Oracle. I know that there is an Oracle version of dateadd, but I can't think of it off the top of my head.

You did mention that you were using Oracle, but it didn't click in my head. I'd recommend checking in the Oracle forum and see what they say.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)