0

I have a following problem. I have this table:

day | valueA | valueB
2022-04-24 04:35:08 2929 | 5845
2022-04-24 06:30:10 2929 | 5844
2022-04-24 07:25:12 2929 | 5844
2022-04-24 08:00:12 2929 | 7844
2022-04-24 12:15:10 2929 | 5844
2022-04-24 14:10:09 2929 | 5844

I would like to get valueB - valueA only if it is 8:00. So from the example above I would get 4915, because 7844-2929.

I tried this based on this answer, which is however about MSSQL: Extracting hours from a DateTime (SQL Server 2005) but it does not work:

SELECT valueB - valueA AS diff
FROM table
WHERE day(HOUR, GETDATE()) = 8

I got that MySQLdb._exceptions.OperationalError: (1305, 'FUNCTION table.day does not exist'). How can I do this in MySQL, please?

vojtam
  • 1,157
  • 9
  • 34
  • Well you're using mysql, the question you linked is about MSSQL Server and you just copied something from the question – crimson589 Apr 25 '22 at 06:54
  • Please refer to manual https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_hour – P.Salmon Apr 25 '22 at 07:01

3 Answers3

2

Try

SELECT valueB - valueA AS diff
FROM table
WHERE HOUR(day) = 8
crimson589
  • 1,238
  • 1
  • 20
  • 36
1

If you are only interested in 8:00 you also need to check for the minutes

SELECT valueB - valueA AS diff
FROM table
WHERE HOUR(day) = 8 AND MINUTE(day) = 0
Juan
  • 3,675
  • 20
  • 34
0

That should work:

SELECT valueB - valueA AS diff
FROM table
WHERE HOUR(day) = 8
Adrian H.
  • 13
  • 3