CODEWITHSUNDEEP
sqloracle-database
s-o-i-a
s-o-i-a

Reputation: 25

Select data between timestamps

Usecase: Query to select the records for a whole day and it should run regularly.

This is my query.

Select to_char(in_date + interval '12' hour, 'DD-MON-YYYY HH24:MI:SS') 
from my_table 
where incoming_date > sysdate-2 and incoming_date < sysdate

I need to select yesterday's data only. Because of the conversion in the select statement I got today's data also. How do I select only yesterday's data? My DB is in UTC+7.00 standard. I need to display it in local standard so that I did a conversion in select statement. And how do I display only yesterday's data? I'm stuck. Please help me

Upvotes: 0

Views: 649

Answers (5)

MT0
MT0

Reputation: 167962

My DB is in UTC+7.00 standard. I need to display it in local standard so that I did a conversion in select statement.

Using a magic value (INTERVAL '12' HOUR) does not describe what it means or the assumptions you made when chosing that value. Instead you can better describe the process by using FROM_TS( timestampvalue, timezonestring ) to convert the value from a TIMESTAMP to a TIMESTAMP WITH TIME ZONE data type and then use AT LOCAL TIME to convert it to the local time. Then if you have daylight savings time or port the query to another international location then it will still display in the current local time. Like this:

SELECT TO_CHAR(
         FROM_TZ( CAST( in_date AS TIMESTAMP ), '+07:00' ) AT LOCAL TIME,
         'DD-MON-YYYY HH24:MI:SS'
       ) 
FROM   my_table 
WHERE  incoming_date >= TRUNC( SYSDATE ) - INTERVAL '1' DAY
AND    incoming_date <  TRUNC( SYSDATE )

And how do I display only yesterday's data?

TRUNC( SYSDATE ) will truncate today's date back to midnight. To get yesterday's data then you can get values that are greater or equal to TRUNC( SYSDATE ) - INTERVAL '1' DAY (one day before midnight today) and also less than TRUNC( SYSDATE ) (midnight today).

Upvotes: 1

AK47
AK47

Reputation: 58

Usually I compare the date only using Trunc.

WHERE trunc(incoming_date) = trunc(sysdate-1)

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65218

only to get the

yesterday's data

make your WHERE condition as

incoming_date between trunc(sysdate) - interval '1' day and trunc(sysdate) - interval '1' second

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

To get all data from yesterday you should use

SELECT TO_CHAR(IN_DATE + INTERVAL '12' HOUR, 'DD-MON-YYYY HH24:MI:SS')
  FROM MY_TABLE
  WHERE INCOMING_DATE BETWEEN TRUNC(SYSDATE) - INTERVAL '1' DAY
                          AND TRUNC(SYSDATE) - INTERVAL '1' SECOND

If, for example, SYSDATE is 05-NOV-2017 18:56:35, the time interval used in the BETWEEN comparison will be from 04-NOV-2017 00:00:00 to 04-NOV-2017 23:59:59. BETWEEN comparisons are inclusive of both endpoints so this will only return data with an INCOMING_DATE of sometime on 04-NOV-2017, in this example.

Best of luck.

Upvotes: 2

Guillaume Mercier
Guillaume Mercier

Reputation: 401

I'm not exactly sure I get your question, but I think I can explain some stuff.

I'll be assuming your table is a bit like this:

 date_added | some_data | some_more_data 
------------|-----------|----------------
 date       | data1     | data2

As I understand your goal is to fetch all the rows that were added to a table the day before the query is run using a select statement. but your current attempt fails at doing so by also returning today's results.

Here is what's happening (I think):
SYSDATE doesn't just give you the current date, it also gives you the time. You can see that for your self by simply altering your current session and setting the date/time format to one that includes both time and date 

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

The reason why you would be getting today's rows is simple, your query is asking for all the rows who's date_added field is between right now and right now - 24 hours. Not today and today - 24 hours.

So what is the solution?
Use the TRUNC function to trim the SYSDATE to the day instead!
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm 

SELECT
    T.*
FROM
    MY_TABLE T
WHERE
    T.DATE_ADDED BETWEEN (TRUNC(SYSDATE,'day') - 1) AND TRUNC(SYSDATE,'day');

As you did mention timezones being a thing keep in mind that SYSDATE returns the date on the server itself and not your computer's.
More on that here: https://stackoverflow.com/a/17925834/7655979

Upvotes: 0

Related Questions