How to List Records with Date from the Last 10 Days

How to list records with date from the last 10 days?

Yes this does work in PostgreSQL (assuming the column "date" is of datatype date)
Why don't you just try it?

The standard ANSI SQL format would be:

SELECT Table.date 
FROM Table 
WHERE date > current_date - interval '10' day;

I prefer that format as it makes things easier to read (but it is the same as current_date - 10).

Select data for last 10 days

You just need to chop off the time portion from date and group by this:

SELECT DATE(created_at) AS Date, SUM(amount) AS Total_Amount, COUNT(*) AS Num_Transactions
FROM t
GROUP BY DATE(created_at)

To restrict the results to last 10 days use the following where clause:

-- 10 days including today
WHERE created_at >= CURRENT_DATE - INTERVAL 9 DAY
AND   created_at  < CURRENT_DATE + INTERVAL 1 DAY

-- 10 days excluding today
WHERE created_at >= CURRENT_DATE - INTERVAL 10 DAY
AND   created_at  < CURRENT_DATE

-- 10 days from current time
WHERE created_at  > CURRENT_TIMESTAMP - INTERVAL 10 DAY
AND   created_at <= CURRENT_TIMESTAMP

Get rows for the last 10 dates

This looks unsuspicious, but it's a hell of a question.

Assumptions

• Your counts are integer.
• All columns in table book are defined NOT NULL.

• The composite (name, sid, date) is unique in table book. You should have a UNIQUE constraint, preferably (for performance) with columns in this order:

  UNIQUE(sid, date, name)
  

  This provides the index needed for performance automatically. (Else create one.) See:

  • Multicolumn index and performance
  • Is a composite index also good for queries on the first field?

crosstab() queries

To get top performance and short query strings (especially if you run this query often) I suggest the additional module tablefunc providing various crosstab() functions. Basic instructions:

• PostgreSQL Crosstab Query

Basic queries

You need to get these right first.

The last 10 days:

SELECT DISTINCT date
FROM   book
WHERE  sid = 1
ORDER  BY date DESC
LIMIT  10;

Numbers for last 10 days using the window function dense_rank():

SELECT *
FROM  (
   SELECT name
        , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
        , count
   FROM   book
   WHERE  sid = 1
   ) sub
WHERE  date_rnk < 11
ORDER  BY name, date_rnk DESC;

(Not including actual dates in this query.)

Column names for output columns (for full solution):

SELECT 'bookname, "' || string_agg(to_char(date, 'DD/MM/YYYY'), '", "' ORDER BY date) || '"'
FROM  (
   SELECT DISTINCT date
   FROM   book
   WHERE  sid = 1
   ORDER  BY date DESC
   LIMIT  10
   ) sub;

Simple result with static column names

This may be good enough for you - but we don't see actual dates in the result:

SELECT * FROM crosstab(
  'SELECT *
   FROM  (
      SELECT name
           , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
           , count
      FROM   book
      WHERE  sid = 1
      ) sub
   WHERE  date_rnk < 11
   ORDER  BY name, date_rnk DESC'
, 'SELECT generate_series(10, 1, -1)'
 ) AS (bookname text
     , date1 int, date2 int, date3 int, date4 int, date5 int
     , date6 int, date7 int, date8 int, date9 int, date10 int);

For repeated use I suggest you create this (very fast) generic C function for 10 integer columns once, to simplify things a bit:

CREATE OR REPLACE FUNCTION crosstab_int10(text, text)
  RETURNS TABLE (bookname text
               , date1 int, date2 int, date3 int, date4 int, date5 int
               , date6 int, date7 int, date8 int, date9 int, date10 int)
  LANGUAGE C STABLE STRICT AS
'$libdir/tablefunc','crosstab_hash';

Details in this related answer:

• Dynamically generate columns for crosstab in PostgreSQL

Then your call becomes:

SELECT * FROM crosstab(
  'SELECT *
   FROM  (
      SELECT name
           , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
           , count
      FROM   book
      WHERE  sid = 1
      ) sub
   WHERE  date_rnk < 11
   ORDER  BY name, date_rnk DESC'
, 'SELECT generate_series(10, 1, -1)'
 );  -- no column definition list required!

Full solution with dynamic column names

Your actual question is more complicated, you also want dynamic column names.

For a given table, the resulting query could look like this then:

SELECT * FROM crosstab_int10(
  'SELECT *
   FROM  (
      SELECT name
           , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
           , count
      FROM   book
      WHERE  sid = 1
      ) sub
   WHERE  date_rnk < 11
   ORDER  BY name, date_rnk DESC'
, 'SELECT generate_series(10, 1, -1)'
   ) AS t(bookname
        , "04/11/2015", "05/11/2015", "06/11/2015", "07/11/2015", "08/11/2015"
        , "09/11/2015", "10/11/2015", "11/11/2015", "15/11/2015", "17/11/2015");

The difficulty is to distill dynamic column names. Either assemble the query string by hand, or (much rather) let this function do it for you:

CREATE OR REPLACE FUNCTION f_generate_date10_sql(_sid int = 1) 
  RETURNS text
  LANGUAGE sql AS
$func$
SELECT format(
 $$SELECT * FROM crosstab_int10(
  'SELECT *
   FROM  (
      SELECT name
           , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
           , count
      FROM   book
      WHERE  sid = %1$s
      ) sub
   WHERE  date_rnk < 11
   ORDER  BY name, date_rnk DESC'
, 'SELECT generate_series(10, 1, -1)'
   ) AS ct(bookname, "$$
|| string_agg(to_char(date, 'DD/MM/YYYY'), '", "' ORDER BY date) || '")'
 , _sid)
FROM  (
   SELECT DISTINCT date
   FROM   book
   WHERE  sid = 1
   ORDER  BY date DESC
   LIMIT  10
   ) sub
$func$;

Call:

SELECT f_generate_date10_sql(1);

This generates the desired query, which you execute in turn.

db<>fiddle here

Count data for last 10 days including days without any entries

Keep your join LEFT , stop using LEFT columns in NULL -prohibiting predicates in WHERE. Include it in ON

with amonth(day) as
(
   select @end as day
       union all
   select day + 1
       from amonth
       where day < CAST(@start as date)
)
select CAST(amonth.day as date) as dat, count(vrijeme) as c 
from amonth 
left join Dnevnik on CAST(vrijeme as date) = CAST(amonth.day as date) 
                     and tipZapisa = 6
group by CAST(amonth.day as date) order by dat 

SQL to get all data for the last 30 days from a PostgreSQL database

Just use current_timestamp (documented here):

select *
from school
where created_time > current_timestamp - interval '30 day';

Return last 10 days data by date using Entity Framework

var tenDaysAgo = DateTime.Today.AddDays(-10);
var Chart = dbcontext.CampaignEmails.Where(x => x.DateSigned >= tenDaysAgo).ToList();

Is what you are looking for i guess.
If you only want 10 records you can use Take() LINQ method before the ToList() call.
Furthermore, you may need to order your results before even accessing them with a OrderBy().

---

Related Topics