Re: TIMESTAMP query


"DA Morgan" <damorgan@xxxxxxxxx> a écrit dans le message de news: 1125389007.165514@xxxxxxxxx
| 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@xxxxxxxxxxxxxxxx
| (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


.

Relevant Pages

  • Re: LINQ Where 1=0
    ... The LinqDataSource control stores values for all primary keys ... Before LINQ to SQL updates or deletes data, it checks the values in view ... If the underlying data source contains a timestamp field that is ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: More questions about porting from MySQL to MS SQL
    ... `ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update ... I learned the hard way that MS SQL does not like ... FUNCTION and CREATE VIEW need to be the first statement in a script. ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: Cannot edit data in a Query
    ... I would check the SQL Database table and make sure it has a primary key AND that it has a timestamp field. ... TimeStamp is a data type that exposes automatically generated, unique binary numbers within a database. ...
    (microsoft.public.access.queries)
  • Re: Form error????
    ... I stated below that a TimeStamp was a GUID. ... Kind of confusing now that (in SQL 2008) there is a new data type/alias ... of a linked SQL Server table ...
    (microsoft.public.access.forms)
  • Re: TIMESTAMP instead of new/old row
    ... As far as the Timestamp goes, I have but one column in the SQL table, ... >> public class ItemLoc ... >> public string Loc_PartNo; ...
    (microsoft.public.dotnet.languages.csharp)