sqloracle-databaseshelltimestampsysdate

Get data of last Month day by day in oracle sql

I want to get data from last month day by day, I can get the last 30 days but I just want the month as it may be less or more than 30 days,

this is the query for getting the last 30 days

SELECT Trunc(timestamp),
       Count(*)
FROM   table1
WHERE  Trunc(timestamp) > Trunc(sysdate - 30)
GROUP  BY Trunc(timestamp)
ORDER  BY 1;

Also, I am using it in a shell script if I can make a variable in the script and put it the query

Solution

  • To get data from the start of the current month until today:

    SELECT TRUNC(timestamp) AS day,
       COUNT(*)
FROM   table1
WHERE  timestamp >= TRUNC(SYSDATE, 'MM')
AND    timestamp <  TRUNC(SYSDATE) + INTERVAL '1' DAY
GROUP BY TRUNC(timestamp)
ORDER BY day

    To get data from the same day last month until today:

    SELECT TRUNC(timestamp) AS day,
       COUNT(*)
FROM   table1
WHERE  timestamp >= ADD_MONTHS(TRUNC(SYSDATE), -1)
AND    timestamp <  TRUNC(SYSDATE) + INTERVAL '1' DAY
GROUP BY TRUNC(timestamp)
ORDER BY day

    db<>fiddle here