Login or Register to Ask a Question and Join Our Community


Programming

SQL date for last seven days

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming SQL date for last seven days
# 1  
Old 04-08-2013
SQL date for last seven days
Hi team,

i have a condition in sql where we have as below
Code:
select sum(column_A)
from temp_t 
where date >= tdb_datestr_to_epoch('21/11/1995 10:00:00','dd/mm/yyyy hh24:mi:ss')
and date < tdb_datestr_to_epoch('22/11/1995 10:00:00','dd/mm/yyyy hh24:mi:ss')

the problems is i have to do the above for 7 days repeatedly so im trying to automated this

the ones in orange are DD from day filed and they should be consecutive

i tried this
Code:
DECLARE
i number(1);
BEGIN
i:=7;
while (i > 0 )
   LOOP  
DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE-i,'DD/MM/YYYY'));
 i:= i -1;
 END LOOP;
END;

so now how to input these in to the the above sql query i.e in > condition it should if its 12/01/2013 then in < condition the date should be 13/01/2013[/CODE][/COLOR]

i have knowledge in shell,awk as well

Thanks all

happy to learn new things
# 2  
Old 04-08-2013
Why do you need a PL/SQL for this? You can provide date range in your SQL itself.

E.g:
Code:
select sum(column_A) from temp_t where date >= SYSDATE - 7 and date <  SYSDATE ;

I guess tdb_datestr_to_epoch is a user-defined function, never seen it before! You can use to_char to format the SYSDATE as per the function argument.

---------- Post updated at 15:47 ---------- Previous update was at 15:43 ----------

Something like:
Code:
select sum(column_A) from temp_t 
where 
date >= tdb_datestr_to_epoch(to_char(SYSDATE - 7, 'dd/mm/yyyy hh24:mi:ss')) and 
date <  tdb_datestr_to_epoch(to_char(SYSDATE, 'dd/mm/yyyy hh24:mi:ss')) ;

# 3  
Old 04-08-2013
Quote:
Originally Posted by Yoda
Why do you need a PL/SQL for this? You can provide date range in your SQL itself.

E.g:
Code:
select sum(column_A) from temp_t where date >= SYSDATE - 7 and date <  SYSDATE ;

I guess tdb_datestr_to_epoch is a user-defined function, never seen it before! You can use to_char to format the SYSDATE as per the function argument.

---------- Post updated at 15:47 ---------- Previous update was at 15:43 ----------

Something like:
Code:
select sum(column_A) from temp_t 
where 
date >= tdb_datestr_to_epoch(to_char(SYSDATE - 7, 'dd/mm/yyyy hh24:mi:ss')) and 
date <  tdb_datestr_to_epoch(to_char(SYSDATE, 'dd/mm/yyyy hh24:mi:ss')) ;


the problem in the above is im taking sum only for specific hours of day and hence for example for 10AM to 10AM and its fixed for all days also

tdb_datestr_to_epoch is a function which will convert the date to epoch form


i also thought of an idea :
1.to take last seven days dates from current days in to one file and fixed hours(like 10:00 ) as said above in to another file the join them with space in between


but problems is how do pass the values from the file consecutively in to sql
# 4  
Old 04-08-2013
OK, If you have them in a file, then you can read them using a while loop into a variable and use it in your SQL:
Code:
tdb_datestr_to_epoch('$VAR','dd/mm/yyyy hh24:mi:ss')

# 5  
Old 04-08-2013
You can find you starting point and add 86400 to it for every 12 hours like this:
for example epoch time for 4/8/2013 10:00:00 GMT is 1365415200

Like below in Shell script:

Code:
FTime=1365415200 
for i in 1 2 3 4 5 6 7
do
STime=`echo "$FTime + ( 86400 * ( $i - 1 ) )" | bc`
ETime=`echo "$FTime + ( 86400 * $i )" | bc`

SQL part:

Code:
date >= ${STime} and date < ${ETime}

This User Gave Thanks to stuckinboredom For This Post:
zozoo
# 6  
Old 04-08-2013
Quote:
Originally Posted by zozoo
...
i have a condition in sql where we have as below
Code:
select sum(column_A)
from temp_t 
where date >= tdb_datestr_to_epoch('21/11/1995 10:00:00','dd/mm/yyyy hh24:mi:ss')
and date < tdb_datestr_to_epoch('22/11/1995 10:00:00','dd/mm/yyyy hh24:mi:ss')

the problems is i have to do the above for 7 days repeatedly so im trying to automated this

the ones in orange are DD from day filed and they should be consecutive
...
so now how to input these in to the the above sql query i.e in > condition it should if its 12/01/2013 then in < condition the date should be 13/01/2013...
I don't know the structure of your table or the data in it, so let's assume the data looks like this:

Code:
SQL>
SQL> --
SQL> select id,
  2         TO_CHAR (dt, 'DD/MM/YYYY HH24:MI:SS') AS dt,
  3         column_a
  4    from temp_t
  5   order by dt
  6  /

        ID DT                    COLUMN_A
---------- ------------------- ----------
         1 14/01/2013 12:00:00        100
         2 15/01/2013 09:55:00        900
         3 15/01/2013 10:00:00        101
         4 16/01/2013 09:00:00        102
         5 16/01/2013 11:00:00        110
         6 16/01/2013 23:00:00        220
         7 17/01/2013 09:50:00        330
         8 18/01/2013 15:00:00        111
         9 19/01/2013 04:00:00        122
        10 20/01/2013 17:00:00        444
        11 21/01/2013 06:00:00        555
        12 21/01/2013 10:30:00        123
        13 22/01/2013 08:00:00        456
        14 22/01/2013 10:30:00         99
        15 23/01/2013 03:00:00        -99

15 rows selected.

SQL>
SQL>

And let's say you want to fetch the sums of COLUMN_A for dates starting from 15/01/2013 to 21/01/2013.

Now, if you ran your current query for the date: 15/01/2013, then your filter conditions would pick up the two rows in red color - the ones with IDs 3 and 4. And the sum of COLUMN_A would be (101 + 102 =) 203.

If you ran your current query for the date: 16/01/2013, then your filter conditions would pick up the three rows in green color - the ones with IDs 5, 6 and 7. And the sum of COLUMN_A would be (110 + 220 + 330 =) 660.

Similarly for date: 18/01/2013, the sum would be (111 + 122 =) 233.
For date: 20/01/2013, the sum would be (444 + 555=) 999.
For date: 21/01/2013, the sum would be (123 + 456=) 579.

The following query achieves the same result mentioned above:

Code:
SQL>
SQL> --
SQL> -- Given a "start date" of "15-JAN-2013", the following query fetches the sum
SQL> -- of column_a for all dates - from : 15-JAN-2013 10:00:00 AM
SQL> --                               to : 21-JAN-2013 10:00:00 AM
SQL> --
SQL>
SQL> with dt (start_date) as (
  2    select TO_DATE ('15/01/2013', 'DD/MM/YYYY') from dual
  3  ),
  4  dt_range (from_date, to_date) as (
  5    select (start_date + level - 1) + 10/24 AS from_date,
  6           (start_date + level) + 10/24     AS to_date
  7      from dt
  8    connect by level <= 7
  9  )
 10  select
 11         dr.from_date,
 12         SUM (tt.column_a) AS sum
 13    from temp_t tt, dt_range dr
 14   where tt.dt >= dr.from_date
 15     and tt.dt <  dr.to_date
 16   group by dr.from_date
 17   order by dr.from_date
 18  /

FROM_DATE        SUM
--------- ----------
15-JAN-13        203
16-JAN-13        660
18-JAN-13        233
20-JAN-13        999
21-JAN-13        579

5 rows selected.

SQL>
SQL>

Notice that the output is "sparse" i.e. it does not contain every date in the range: 15/01/2013 to 21/01/2013.
The date 17/01/2013 is missing because there are no records in TEMP_T that have a DT value that lies between "17/01/2013 10:00:00 AM" and "18/01/2013 09:59:59" both inclusive.
The date 19/01/2013 is missing because there are no records in TEMP_T that have a DT value that lies between "19/01/2013 10:00:00 AM" and "20/01/2013 09:59:59" both inclusive.

If you still wanted to see those two dates with the sum value of 0 i.e. if you wanted the output to be "dense", then an outer join could be used like so -

Code:
SQL>
SQL> -- Densely populated data
SQL> -- Return **every** date in the range, whether or not
SQL> -- there are any records for that date. If there are
SQL> -- no records for a date, then return 0 as the sum.
SQL>
SQL> with dt (start_date) as (
  2    select TO_DATE ('15/01/2013', 'DD/MM/YYYY') from dual
  3  ),
  4  dt_range (from_date, to_date) as (
  5    select (start_date + level - 1) + 10/24 AS from_date,
  6           (start_date + level) + 10/24     AS to_date
  7      from dt
  8    connect by level <= 7
  9  )
 10  select
 11         dr.from_date,
 12         NVL (SUM (tt.column_a), 0) AS sum
 13    from dt_range dr
 14         left outer join temp_t tt
 15      on (
 16               tt.dt >= dr.from_date
 17           and tt.dt <  dr.to_date
 18         )
 19   group by dr.from_date
 20   order by dr.from_date
 21  /

FROM_DATE        SUM
--------- ----------
15-JAN-13        203
16-JAN-13        660
17-JAN-13          0
18-JAN-13        233
19-JAN-13          0
20-JAN-13        999
21-JAN-13        579

7 rows selected.

SQL>
SQL>

These 2 Users Gave Thanks to durden_tyler For This Post:
jim mcnamara zozoo
# 7  
Old 04-09-2013
Quote:
Originally Posted by durden_tyler
I don't know the structure of your table or the data in it, so let's assume the data looks like this:

Code:
SQL>
SQL> --
SQL> select id,
  2         TO_CHAR (dt, 'DD/MM/YYYY HH24:MI:SS') AS dt,
  3         column_a
  4    from temp_t
  5   order by dt
  6  /

        ID DT                    COLUMN_A
---------- ------------------- ----------
         1 14/01/2013 12:00:00        100
         2 15/01/2013 09:55:00        900
         3 15/01/2013 10:00:00        101
         4 16/01/2013 09:00:00        102
         5 16/01/2013 11:00:00        110
         6 16/01/2013 23:00:00        220
         7 17/01/2013 09:50:00        330
         8 18/01/2013 15:00:00        111
         9 19/01/2013 04:00:00        122
        10 20/01/2013 17:00:00        444
        11 21/01/2013 06:00:00        555
        12 21/01/2013 10:30:00        123
        13 22/01/2013 08:00:00        456
        14 22/01/2013 10:30:00         99
        15 23/01/2013 03:00:00        -99

15 rows selected.

SQL>
SQL>

And let's say you want to fetch the sums of COLUMN_A for dates starting from 15/01/2013 to 21/01/2013.

Now, if you ran your current query for the date: 15/01/2013, then your filter conditions would pick up the two rows in red color - the ones with IDs 3 and 4. And the sum of COLUMN_A would be (101 + 102 =) 203.

If you ran your current query for the date: 16/01/2013, then your filter conditions would pick up the three rows in green color - the ones with IDs 5, 6 and 7. And the sum of COLUMN_A would be (110 + 220 + 330 =) 660.

Similarly for date: 18/01/2013, the sum would be (111 + 122 =) 233.
For date: 20/01/2013, the sum would be (444 + 555=) 999.
For date: 21/01/2013, the sum would be (123 + 456=) 579.

The following query achieves the same result mentioned above:

Code:
SQL>
SQL> --
SQL> -- Given a "start date" of "15-JAN-2013", the following query fetches the sum
SQL> -- of column_a for all dates - from : 15-JAN-2013 10:00:00 AM
SQL> --                               to : 21-JAN-2013 10:00:00 AM
SQL> --
SQL>
SQL> with dt (start_date) as (
  2    select TO_DATE ('15/01/2013', 'DD/MM/YYYY') from dual
  3  ),
  4  dt_range (from_date, to_date) as (
  5    select (start_date + level - 1) + 10/24 AS from_date,
  6           (start_date + level) + 10/24     AS to_date
  7      from dt
  8    connect by level <= 7
  9  )
 10  select
 11         dr.from_date,
 12         SUM (tt.column_a) AS sum
 13    from temp_t tt, dt_range dr
 14   where tt.dt >= dr.from_date
 15     and tt.dt <  dr.to_date
 16   group by dr.from_date
 17   order by dr.from_date
 18  /

FROM_DATE        SUM
--------- ----------
15-JAN-13        203
16-JAN-13        660
18-JAN-13        233
20-JAN-13        999
21-JAN-13        579

5 rows selected.

SQL>
SQL>

Notice that the output is "sparse" i.e. it does not contain every date in the range: 15/01/2013 to 21/01/2013.
The date 17/01/2013 is missing because there are no records in TEMP_T that have a DT value that lies between "17/01/2013 10:00:00 AM" and "18/01/2013 09:59:59" both inclusive.
The date 19/01/2013 is missing because there are no records in TEMP_T that have a DT value that lies between "19/01/2013 10:00:00 AM" and "20/01/2013 09:59:59" both inclusive.

If you still wanted to see those two dates with the sum value of 0 i.e. if you wanted the output to be "dense", then an outer join could be used like so -

Code:
SQL>
SQL> -- Densely populated data
SQL> -- Return **every** date in the range, whether or not
SQL> -- there are any records for that date. If there are
SQL> -- no records for a date, then return 0 as the sum.
SQL>
SQL> with dt (start_date) as (
  2    select TO_DATE ('15/01/2013', 'DD/MM/YYYY') from dual
  3  ),
  4  dt_range (from_date, to_date) as (
  5    select (start_date + level - 1) + 10/24 AS from_date,
  6           (start_date + level) + 10/24     AS to_date
  7      from dt
  8    connect by level <= 7
  9  )
 10  select
 11         dr.from_date,
 12         NVL (SUM (tt.column_a), 0) AS sum
 13    from dt_range dr
 14         left outer join temp_t tt
 15      on (
 16               tt.dt >= dr.from_date
 17           and tt.dt <  dr.to_date
 18         )
 19   group by dr.from_date
 20   order by dr.from_date
 21  /

FROM_DATE        SUM
--------- ----------
15-JAN-13        203
16-JAN-13        660
17-JAN-13          0
18-JAN-13        233
19-JAN-13          0
20-JAN-13        999
21-JAN-13        579

7 rows selected.

SQL>
SQL>

Hi Durden,
Thanks a lot for detailed reply , but my table has records with date format in epoch time where shall i modify itSmilie
Last edited by zozoo; 04-09-2013 at 03:37 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. HP-UX

awk command in hp UNIX subtract 30 days automatically from current date without date illegal option

current date command runs well awk -v t="$(date +%Y-%m-%d)" -F "'" '$1 < t' myname.dat subtract 30 days fails awk -v t="$(date --date="-30days" +%Y-%m-%d)" -F "'" '$1 < t' myname.dat awk command in hp unix subtract 30 days automatically from current date without date illegal option error... (20 Replies)
Discussion started by: kmarcus
20 Replies

2. UNIX for Beginners Questions & Answers

Subscribers with Date 90 days older than current date

I have to display only those subscribers which are in "unconnected state" and the date is 90 days older than today's date. Below command is used for this purpose: cat vfsubscriber_20170817.csv | sed -e 's/^"//' -e '1d' | nawk -F '",' '{if ( (substr($11,2,4) == 2017) && ( substr($11,2,8) -lt... (1 Reply)
Discussion started by: dia
1 Replies

3. Shell Programming and Scripting

UNIX date fuction - how to deduct days from today's date

Hi, One of my Unix scripts needs to look for files coming in on Fridays. This script runs on Mondays. $date +"%y%m%d" will give me today's date. How can I get previous Friday's date.. can I do "today's date minus 3 days" to get Friday's date? If not, then any other way?? Name of the files is... (4 Replies)
Discussion started by: juzz4fun
4 Replies

4. Shell Programming and Scripting

Adding days to system date then compare to a date

Hi! I am trying to read a file and every line has a specific date as one of its fields. I want to take that date and compare it to the date today plus 6 days. while read line do date=substr($line, $datepos, 8) #date is expected to be YYYYMMDD if ; then ...proceed commands ... (1 Reply)
Discussion started by: kokoro
1 Replies

5. Shell Programming and Scripting

Number of days between the current date and user defined date

I am trying to find out the number of days between the current date and user defined date. I took reference from here for the date2jd() function. Modified the function according to my requirement. But its not working properly. Original code from here is working fine. #!/bin/sh... (1 Reply)
Discussion started by: hiten.r.chauhan
1 Replies

6. Shell Programming and Scripting

How to Get 60 days Old date from current date in KSH script

Hi i am writing a cron job. so for it i need the 60 days old date form current date in variable. Like today date is 27 jan 2011 then output value will be stote in variable in formet Nov 27. i am using EST date, and tried lot of solution and see lot of post but it did not helpful for me. so... (3 Replies)
Discussion started by: Himanshu_soni
3 Replies

7. Shell Programming and Scripting

Date after 5 days from current date in YYYYMMDD format

Hello Experts, How do i get date after 5 days from current date in YYYYMMDD format? How do you compare date in YYYYMMDD format? Thanks (8 Replies)
Discussion started by: needyourhelp10
8 Replies

8. Shell Programming and Scripting

how to get what date was 28 days ago of the current system date IN UNIX

Hi, Anybody knows how to get what date was 28 days ago of the current system date through UNIX script. Ex : - If today is 28th Mar 2010 then I have to delete the files which arrived on 1st Mar 2010, (15 Replies)
Discussion started by: kandi.reddy
15 Replies

9. Shell Programming and Scripting

date for two days or 3 days ago

i need a script that can tell me the date 2 days ago or 3 days ago. please help (7 Replies)
Discussion started by: tomjones
7 Replies

10. Shell Programming and Scripting

How to find a date which is 7 days past when given current date

hii all. I have to get the date of the 7th day past from the current date. if i give the current date as sep 3 then i must get the date as 27th of august. can we get the values from the "cal" command. cal | awk '{print $2}' will this type of command work. actually my need is if today is... (17 Replies)
Discussion started by: ladtony
17 Replies
Login or Register to Ask a Question