0

Here is what I did:

Select count(check_id)
From Checks
Where timestamp::date > '2012-07-31'
Group by 1

Is it right to do it like I did or is there a better way? Should/could I have used the DateDIFF function in my WHERE clause? Something like: DATEDIFF(day, timestamp, '2012/07/31') > 0

Also, I need to figure out how I'd calculate the total rate of acceptance for this time period? Can anyone provide their expertise with this?

Diablo
  • 1
  • 2
  • 2
    `GROUP BY 1` makes no sense in this query. – Gordon Linoff Nov 12 '20 at 14:21
  • Thanks so much @GordonLinoff and that group by was a mistake. Quick follow up, can you please help figure out how to calculate the total rate of acceptance for this time period here? – Diablo Nov 12 '20 at 15:26

1 Answers1

1

Is it right to do it like I did or is there a better way?

Using a cast like that is a perfectly valid way to convert a timestamp to a date (I don't understand the reference to the non-existing datediff though - why would adding anything to a timestamp change it)

However, the cast has one drawback: if there is an index on the column "timestamp" it won't be used.

But as you just want a range after a certain date, there is no reason to cast the column to begin with.

The following will achieve the same thing as your query, but can make use of an index on the column "timestamp" in case there is one and using it is considered beneficial by the optimizer.

Select count(distinct check_id)
From Checks
Where "timestamp" > date '2012-07-31' + 1

Note the + 1 which selects the day after, otherwise the query would include rows that are on that date but after midnight.

I removed the unnecessary group by from your query.

If you want to get a count per day, then you will need to include the day in the SELECT list. In that case casting is a good way to do it:

Select "timestamp"::date, count(distinct check_id)
From Checks
Where "timestamp" > date '2012-07-31' + 1
group by "timestamp"::date
  • Casting to date and the `GROUP BY 1` would make sense in case original idea was to find count per day, and the day field was removed from the query as an attempt to simplify the query. Just an idea... – Vesa Karjalainen Nov 12 '20 at 14:26
  • @VesaKarjalainen: good point, thanks. I have amended my answer. –  Nov 12 '20 at 14:28