|
Home > Archive > Oracle Database Server > August 2005 > TIMESTAMP query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| June Moore 2005-08-30, 2:54 am |
| Can you pls tell me how to select where a TIMESTAMP date is N hours
ago?
select * from <table> where job_date ... ?
thanks
JM
| |
| DA Morgan 2005-08-30, 8:08 am |
| 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@x.washington.edu
(replace x with u to respond)
| |
| Michel Cadot 2005-08-30, 5:59 pm |
|
"DA Morgan" <damorgan@psoug.org> a écrit dans le message de news: 1125389007.165514@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@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
| |
| DA Morgan 2005-08-30, 5:59 pm |
| Michel Cadot wrote:
> "DA Morgan" <damorgan@psoug.org> a écrit dans le message de news: 1125389007.165514@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@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@x.washington.edu
(replace x with u to respond)
|
|
|
|
|