SQL FOR TIME STAMP

[fernandezd]

Hi

I want to be able to get the current timestamp from a database by using select statements.

How can this be done?



FernandezD
At your service.
Unix systems Admin

 

[sem]

Use SYSDATE function:

select SYSDATE from dual.

If its precision doesn't suit you, use CURRENT_TIMESTAMP (works since 9i)

Regards, Dima

http://seminihin.narod.ru

 

[jimbopalmer]

Oracle 'dates' are always accurate to the second unless you truncate them, so

Select sysdate from dual;

just needs formatting to show the time.

Select to_char(sysdate, 'HH:MI:SS') from dual;

as an example

I tried to remain child-like, all I acheived was childish.

 

[fernandezd]

Hi,

This is my dilema.
I have a field called Create_time for records.
It is a CREATE_TIME which is a NUMBER(15).
Now I think this is the number of seconds since 1970.

I have to compare this to actual time like say
select sysdate from dual;But this is a differnet format.
What I would like to do would be to be able to get time now in the same format as the CREATE_TIME field so as to compate them.

Can any one help........ PLEASE
This is a little output from the table.


CREATE_TIME
-----------
1063887715
1063888362
1063888896
1063886482
1063889897
1063890021
1063890134


FernandezD
At your service.
Unix systems Admin

 

[ddrillich]

Please try:

select to_date('01011970','DDMMYYYY') + 1063887715/86400 from dual;

86400 is the number of seconds in a day.

it returns: Sep 18 2003 12:21:55:000PM

btw, it seems that somebody in your place implemented the new 9i INTERVAL DAY TO SECOND data type using the NUMBER data type.

Regards,
Dan

 

[SantaMufasa]

Sr./Sra. Fernandez,

You are encountering the format named "Universal Time Coordinated (UTC)". Here are functions that transform "Oracle dates to UTC" (UTC_FROM_ORACLE) and from "UTC to Oracle dates" (UTC_TO_ORACLE). They should help you compare date in either direction:

==========================================================
create or replace function utc_from_oracle (date_in in date) return number is
begin
	return (date_in-to_date('01.01.70','dd.mm.rr'))*(60*60*24);
end;
/
==========================================================
create or replace function utc_to_oracle (utc_in in number) return date is
begin
	return to_date('01.01.70','dd.mm.rr')+(utc_in/(60*60*24));
end;
/
==========================================================
Here are proofs-of-concept using your values:

SQL> col a heading "UTC values to Oracle Dates" format a65
SQL> select '"'||create_time||'" in UTC value = "'||
  2   to_char(utc_to_oracle(create_time),'yyyy-mm-dd hh24:mi:ss')||
  3   '" in Oracle date.' a
  4  from utc_values
  5  /

UTC values to Oracle Dates
-----------------------------------------------------------------
"1063887715" in UTC value = "2003-09-18 12:21:55" in Oracle date.
"1063888362" in UTC value = "2003-09-18 12:32:42" in Oracle date.
"1063888896" in UTC value = "2003-09-18 12:41:36" in Oracle date.
"1063886482" in UTC value = "2003-09-18 12:01:22" in Oracle date.
"1063889897" in UTC value = "2003-09-18 12:58:17" in Oracle date.
"1063890021" in UTC value = "2003-09-18 13:00:21" in Oracle date.
"1063890134" in UTC value = "2003-09-18 13:02:14" in Oracle date.

7 rows selected.

SQL> col b heading "Oracle Dates to UTC values" format a65
SQL> select '"'||to_char(ODate,'yyyy-mm-dd hh24:mi:ss')||
  2   '" in Oracle date = "'||utc_from_oracle(ODate)||'" in UTC value.' b
  3  from Oracle_dates;

Oracle Dates to UTC values
-----------------------------------------------------------------
"2003-09-18 12:21:55" in Oracle date = "1063887715" in UTC value.
"2003-09-18 12:32:42" in Oracle date = "1063888362" in UTC value.
"2003-09-18 12:41:36" in Oracle date = "1063888896" in UTC value.
"2003-09-18 12:01:22" in Oracle date = "1063886482" in UTC value.
"2003-09-18 12:58:17" in Oracle date = "1063889897" in UTC value.
"2003-09-18 13:00:21" in Oracle date = "1063890021" in UTC value.
"2003-09-18 13:02:14" in Oracle date = "1063890134" in UTC value.

7 rows selected.

SQL>

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:07 (09Feb04) UTC (aka "GMT" and "Zulu&quot, 11:07 (09Feb04) Mountain Time)