Re: TIMESTAMP query

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 30 Aug 2005 09:46:38 -0700
Message-ID: <1125420351.942212@yasure>

Michel Cadot wrote:

> "DA Morgan" <damorgan_at_psoug.org> a écrit dans le message de news: 1125389007.165514_at_yasure...

> | June Moore wrote:
> | > Can you pls tell me how to select where a TIMESTAMP date is N hours
> | > ago?
> | >
> | > select * from <table> where job_date ... ?
> | >
> | > thanks
> | > JM
> |
> |
> | SQL*Plus: Release 10.1.0.4.0 - Production on Tue Aug 30 01:03:38 2005
> |
> | Copyright (c) 1982, 2005, Oracle. All rights reserved.
> |
> | Connected to:
> | Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
> | With the Partitioning, OLAP and Data Mining options
> |
> | SQL> select to_char(systimestamp, 'HH:MI:SS') from dual;
> |
> | TO_CHAR(
> | --------
> | 01:03:50
> |
> | SQL> ed
> | Wrote file afiedt.buf
> |
> | 1* select to_char(systimestamp-1/24, 'HH:MI:SS') from dual
> | SQL> /
> |
> | TO_CHAR(
> | --------
> | 12:03:59
> |
> | SQL>
> |
> | --
> | Daniel A. Morgan
> | http://www.psoug.org
> | damorgan_at_x.washington.edu
> | (replace x with u to respond)

> 
> Not fully true (at least on 9i, i don't check it on 10g) as when you use direct artihmetic there is a conversion from timestamp to 
> date datatype, so you lose fractional:
> 
> SQL> select to_char(systimestamp,'HH24:MI:SS.FF6') from dual;
> TO_CHAR(SYSTIMESTA
> ------------------
> 15:40:38.181000
> 
> 1 row selected.
> 
> SQL> select to_char(systimestamp-1/24,'HH24:MI:SS.FF6') from dual;
> select to_char(systimestamp-1/24,'HH24:MI:SS.FF6') from dual
>                                  *
> ERROR at line 1:
> ORA-01821: date format not recognized
> 
> 
> SQL> select to_char(systimestamp-1/24,'HH24:MI:SS') from dual;
> TO_CHAR(S
> ---------
> 14:41:00
> 
> 1 row selected.
> 
> You can use one of this way:
> 
> SQL> select to_char(systimestamp-numtodsinterval(1,'HOUR'),'HH24:MI:SS.FF6') from dual;
> TO_CHAR(SYSTIMESTA
> ------------------
> 14:44:04.698000
> 
> 1 row selected.
> 
> SQL> select to_char(systimestamp-to_dsinterval('0 1:00:00'),'HH24:MI:SS.FF6') from dual;
> TO_CHAR(SYSTIMESTA
> ------------------
> 14:45:33.065000
> 
> 1 row selected.
> 
> SQL> select to_char(systimestamp-interval '1' hour,'HH24:MI:SS.FF6') from dual;
> TO_CHAR(SYSTIMESTA
> ------------------
> 14:47:41.019000
> 
> 1 row selected.
> 
> Regards
> Michel Cadot

You are correct but my interpretation of the OP's request: "tell me how to select" indicated something for the WHERE clause so selected values would not be altered.

Either way I think the OP now has sufficient information to proceed.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Tue Aug 30 2005 - 11:46:38 CDT