SELECT in a time period in a datetime column

Asked

Viewed 46 times

0

I have the following column

id datareg
1  2020-07-24 08:00:00
2  2020-07-24 08:30:00
3  2020-07-24 08:45:00
4  2020-07-24 09:00:00
5  2020-07-24 13:00:00

I need a query that returns the AMOUNT of records within a time interval, for example, from 08h to 09h. I’m doing the consultation this way:

SELECT COUNT(id) from TABLE 
WHERE datareg BETWEEN '08:00:00' AND '09:00:00'

And it returns to me that I have a total of ZERO records. What I’m missing on the query line?

sql mysqli sqlite phpmyadmin
  • 2

    But isn’t your field DATETIME type? Just separate the TIME part.

  • Yes, the datareg is DATETIME type

  • can use 'cast(datareg as date)' or 'date(datareg)'

  • 2

    SELECT COUNT(id) from TABLE 
WHERE TIME(datareg) BETWEEN '08:00:00' AND '09:00:00'.

No answers

Browser other questions tagged sql mysqli sqlite phpmyadmin

You are not signed in. Login or sign up in order to post.