There is a table of votes with dates. There are many entries in one day. You must select the entries for the last three days or three days before the specific date.
I was going to take the maximum date, for each line, perform a datediff relative to the maximum date, we get a number, and select only those lines for which this datediff returns the value <3.
Can you please tell me if I'm doing the right thing, or is there another way?

The table is quite simple.

create table votes (id int, creationDate datetime, userId int)
  • It would be nice to see the structure of the database ... - Streletz
  • @Vega It is better to take the current date or the one you need, subtract 3 from it and select all entries that are larger than the received date. From the dates you can subtract 3 days by the usual arithmetic subtraction. select * from table where date_field>getdate()-3 - Mike
  • @Mike I need a maximum date, it changes. I understand that you need to get max (date), and then how to get -3 days is not clear. - Vega
  • @Streletz structure is simple, I added sql to create it in question. - Vega

1 answer 1

It is better to first obtain the required date, subtract 3 days from it and select records greater than this date:

  select * from votes where creationDate > (select max(creationDate)-3 from votes)

If there is an index across the creationDate field, this will significantly speed up the request, since and getting the maximum and searching for all the big dates will go over it. When applying the same datediff on each record completely eliminates the use of indexes and will lead to a full table scan.

To set a specific date, or calculate in advance -3 days, if it is difficult, then:

  select * from votes where creationDate > Cast('2016-15-01' as datetime)-3

PS Depending on the MsSQL server settings, the date format may be different. year-month-day or year-day-month

  • thank. and how to specify a specific date instead of max (creationDate)? - Vega
  • @Vega supplemented the answer - Mike