Re: TIMESTAMP query

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 30 Aug 2005 15:55:04 +0200
Message-ID: <4314653d$0$20938$626a14ce@news.free.fr>

"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 Received on Tue Aug 30 2005 - 08:55:04 CDT