Where condition with a specific hour and minute from a Timestamp SQL

Question

I have to search for a particular hour and minute within a column of a table.

For example:

activitydate (timestamp without time zone)
---------------------
"2021-02-11 00:00:00"
"2021-02-01 00:00:00"
"2021-02-03 03:00:00"
"2021-02-04 00:00:00"
"2021-02-05 05:00:00"
"2021-02-05 00:00:00"
"2021-02-08 07:00:00"

I wanted to run a query like this:

SELECT *
FROM table_name
WHERE activitydate_hour = '03: 00 '

Is there a way to do it?

question from:https://stackoverflow.com/questions/66066669/where-condition-with-a-specific-hour-and-minute-from-a-timestamp-sql

Answer 1

In standard SQL, you would use:

where extract(hour from activitydate) = 3

However, not all databases support extract(), so your database might have bespoke syntax.