abhiieor abhiieor - 2 months ago 7
SQL Question

timestamp between date range

I am trying to find all record count for each day using query:

select cast(Timestamp_field as date), count(*) from table1 group by 1 having cast(Timestamp_field as date) between date and date -10;


Timestamp_field
is a timestamp and I am casting this to date. This; despite max value of Timestamp_field showing
2016-09-20 12:31:38.000000
, doesn't return any record. Any idea why?

Answer

My guess is that the problem is the between. Perhaps this will work for you:

select cast(Timestamp_field as date), count(*)
from table1
group by 1
having cast(Timestamp_field as date) between date - 10 and date;

The smaller value should go first for the between comparands.

Note: You should do the filtering before the group by, not after:

select cast(Timestamp_field as date), count(*)
from table1
where cast(Timestamp_field as date) between date - 10 and date;
group by 1
Comments