Home » SQL & PL/SQL » SQL & PL/SQL » Between SYSDATE's with Offset?
Between SYSDATE's with Offset? [message #159960] Wed, 22 February 2006 06:42 Go to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

I want to select data only from the previous day, the records has a date field 'TIMESTAMP'. I thought of using 'BETWEEN...AND...', however the previous day starts at 04:00 and runs untill 03:59:59 the following morning.

Using: -

TO_DATE(SA.TIMESTAMP, 'DD.MM.YYYY') BETWEEN TO_DATE(TRUNC(SYSDATE - 1), 'DD.MM.YYYY') AND TO_DATE(TRUNC(SYSDATE), 'DD.MM.YYYY')


The above I understand is using Midnight, am I right?

How can I select only the records where they fall between yesterday 04:00 and this morning 03:59:59?

Any suggestions or advice would be much appreciated.

Regards

Report message to a moderator

Re: Between SYSDATE's with Offset? [message #159965 is a reply to message #159960] Wed, 22 February 2006 08:01 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member
Apply this logic.....


19:28:44 SQL> select sysdate from dual;

SYSDATE
------------------
22-FEB-06 07:22:06

Elapsed: 00:00:00.02
19:28:46 SQL> select sysdate+1/24 from dual;

SYSDATE+1/24
------------------
22-FEB-06 08:22:23

Elapsed: 00:00:00.02
19:29:04 SQL> select sysdate+5/24 from dual;

SYSDATE+5/24
------------------
23-FEB-06 12:22:56

Elapsed: 00:00:00.03


Thumbs Up
Rajuvan

[Updated on: Wed, 22 February 2006 08:01]

Report message to a moderator

Re: Between SYSDATE's with Offset? [message #159968 is a reply to message #159965] Wed, 22 February 2006 08:14 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Thanks for the reply.

In your example 'SYSDATE+5/24' resluted in the correct time, but I noticed it had changed the date to the 23rd?

Also how will this help me with my 'BETWEEN 04:00 AND 03:59'?

Regards

Report message to a moderator

Re: Between SYSDATE's with Offset? [message #159970 is a reply to message #159968] Wed, 22 February 2006 08:26 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
More hints:
trunc(sysdate)+4/24 is 4:00 am

Report message to a moderator

Re: Between SYSDATE's with Offset? [message #159976 is a reply to message #159970] Wed, 22 February 2006 08:46 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Aaah I see...

SQL> select to_char(trunc(sysdate)+4/24, 'dd.mm.yyyy hh24:mi:ss') from dual;

TO_CHAR(TRUNC(SYSDATE)+4/24,'D
---------------------------------------------------------------------------
22.02.2006 04:00:00


What about geting '22.02.2006 03:59:59' though?

Regards

Report message to a moderator

Re: Between SYSDATE's with Offset? [message #159977 is a reply to message #159960] Wed, 22 February 2006 08:48 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member
Because....
Now here in INDIA...... Its 8:00 PM (evening).....

Razz Razz Razz Razz Razz

19:29:37 SQL> alter session set
20:14:45   2   nls_date_format ='dd-MON-yy HH:MI:SS PM'
20:14:49   3  ;

Session altered.

Elapsed: 00:00:00.00
20:14:50 SQL>  select sysdate from dual;

SYSDATE
---------------------
22-FEB-06 08:08:22 PM

Elapsed: 00:00:00.02
20:15:02 SQL> select sysdate+5/24 from dual;

SYSDATE+5/24
---------------------
23-FEB-06 01:08:31 AM

Elapsed: 00:00:00.01
20:15:11 SQL> select trunc(sysdate+1)+5/24 from dual;

TRUNC(SYSDATE+1)+5/24
---------------------
23-FEB-06 05:00:00 AM

Elapsed: 00:00:00.01
20:16:22 SQL>


Is it clear Now ... ???

Thumbs Up
Rajuvan

[Updated on: Wed, 22 February 2006 08:49]

Report message to a moderator

Re: Between SYSDATE's with Offset? [message #159979 is a reply to message #159976] Wed, 22 February 2006 08:56 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
To be safe, I would just use greater than or equal to for your start window, and a simple less than for your end window, instead of a BETWEEN.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'
  2  /
 
Session altered.
 
SQL> COLUMN ts                             FORMAT A30
SQL> COLUMN "MUST_BE_GREATER_THAN_OR_=_TO" FORMAT A30
SQL> CREATE TABLE t (id NUMBER, ts TIMESTAMP)
  2  /
 
Table created.
 
SQL> INSERT INTO t VALUES (1,TO_TIMESTAMP('20060221035959999998','YYYYMMDDHH24MISSFF6'));
SQL> INSERT INTO t VALUES (2,TO_TIMESTAMP('20060221035959999999','YYYYMMDDHH24MISSFF6'));
SQL> INSERT INTO t VALUES (3,TO_TIMESTAMP('20060221040000000000','YYYYMMDDHH24MISSFF6'));
SQL> INSERT INTO t VALUES (4,TO_TIMESTAMP('20060221040000000001','YYYYMMDDHH24MISSFF6'));
SQL> INSERT INTO t VALUES (5,TO_TIMESTAMP('20060222035959999998','YYYYMMDDHH24MISSFF6'));
SQL> INSERT INTO t VALUES (6,TO_TIMESTAMP('20060222035959999999','YYYYMMDDHH24MISSFF6'));
SQL> INSERT INTO t VALUES (7,TO_TIMESTAMP('20060222040000000000','YYYYMMDDHH24MISSFF6'));
SQL> INSERT INTO t VALUES (8,TO_TIMESTAMP('20060222040000000001','YYYYMMDDHH24MISSFF6'));
 
SQL> SELECT id, ts FROM t
  2  /
 
        ID TS
---------- ------------------------------
         1 21-FEB-06 03.59.59.999998 AM
         2 21-FEB-06 03.59.59.999999 AM
         3 21-FEB-06 04.00.00.000000 AM
         4 21-FEB-06 04.00.00.000001 AM
         5 22-FEB-06 03.59.59.999998 AM
         6 22-FEB-06 03.59.59.999999 AM
         7 22-FEB-06 04.00.00.000000 AM
         8 22-FEB-06 04.00.00.000001 AM
 
8 rows selected.
 
SQL> SELECT id
  2  ,      ts
  3  ,      TRUNC(SYSDATE) - 1 + 4/24        "MUST_BE_GREATER_THAN_OR_=_TO"
  4  ,      TRUNC(SYSDATE) + 4/24            must_be_less_than
  5  FROM   t
  6  WHERE  t.ts >= TRUNC(SYSDATE) - 1 + 4/24
  7  AND    t.ts <  TRUNC(SYSDATE) + 4/24
  8  /
 
        ID TS                             MUST_BE_GREATER_THAN_OR_=_TO   MUST_BE_LESS_THAN
---------- ------------------------------ ------------------------------ --------------------
         3 21-FEB-06 04.00.00.000000 AM   21-FEB-2006 04:00:00           22-FEB-2006 04:00:00
         4 21-FEB-06 04.00.00.000001 AM   21-FEB-2006 04:00:00           22-FEB-2006 04:00:00
         5 22-FEB-06 03.59.59.999998 AM   21-FEB-2006 04:00:00           22-FEB-2006 04:00:00
         6 22-FEB-06 03.59.59.999999 AM   21-FEB-2006 04:00:00           22-FEB-2006 04:00:00
 
SQL>

Report message to a moderator

Previous Topic: Multi step procedure query plans
Next Topic: Efficient way of writing this sql ?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Aug 21 20:49:32 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.