DB query - date conversion 1

[pho01]

I have a question on how to run a query on a date comparision. There is a recordcreation column that is measured in time_to_second. For example, right now, it is recorded as 1144942416.

My question is:

when running a query for records that are submitted today, how to convert that recordcreation and compare
recordcreation > to_date '20060413000000','YYYYMMDDHH24MISS');

or any other way just to get records that are submitted today.

Thanks!

 

[SantaMufasa]

PHO,

It is unclear whether the column "recordcreation" is of datatype DATE, NUMBER, or VARCHAR. Presuming that it is of datatype DATE, then you can do the following comparison to obtain records where "recordcreation" is for any time during today's date:

...WHERE recordcreation >= TRUNC(SYSDATE)...

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[pho01]

recordcreation is a number datatype.

 

[SantaMufasa]

If "recordcreation" is of type NUMBER with the contents "1144942416", then that means it is in Universal Time Co-ordinated (UTC). For Oracle to deal with such values, you need a function such as the following to convert UTC values into Oracle DATEs:

UTC-to-Oracle DATE function:

create or replace function UTC_to_DATE (utc_in in number) return date is
begin
    return new_time(to_date('01.01.70','dd.mm.rr')+(utc_in/(60*60*24)),'CDT','GMT');
end;
/

Function created.

(In the above function, replace 'CDT' with the appropriate time-zone-code value of the date/time source.)

Once you have that function, your original code would then appear as:

...WHERE UTC_to_DATE(recordcreation) >= TRUNC(SYSDATE)...

Let us know how this suits.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[pho01]

If you only have read access to the database, and the only thing you can do is to run a query. where would you create that function?

What is trunc(sysdate)? i just need to run a query to get a report based on dates, for example, all records that are submitted today. (recordcreationtime >= 04132006 00:00:00)

thanks!

 

[SantaMufasa]

PHO,

Under these newly identified restrictions, how about:

...WHERE new_time(to_date('01.01.70','dd.mm.rr')
         +(recordcreation/(60*60*24)),'CDT','GMT')
         >= trunc(SYSDATE)...

What is trunc(sysdate)?

"SYSDATE" is the current DATE plus TIME to the current second. It is probable that no value of "recordcreation" will ever be ">=" SYSDATE. Therefore, if you want to see rows where "recordcreation" shares the calendar date with today, then you must strip off the TIME component of SYSDATE via trunc(SYSDATE), which leaves you at "midnight on the morning of the current date".


Let us know if this satisfied the need and answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[pho01]

it seems to work great. Thanks!