CODEWITHSUNDEEP
sqldatabasepostgresql
user526206
user526206

Reputation:

How to extract hour from query in postgres

I have timestamp in my table and i want to extract only hour from it. I search and find a extract function but unable to use as a query. Do i need to convert first timestamp in varchar and then extract hour from it? Here is my query:

select extract(hour from timestamp '2001-02-16 20:38:40') // example

actual query:

select extract(hour from timestamp observationtime) from smartvakt_device_report

Upvotes: 78

Views: 117417

Answers (4)

Phenyl
Phenyl

Reputation: 583

EXTRACT does not work with Grafana but date_part does. The solution for me was:

SELECT date_part('hour', observationtime::TIMESTAMP) FROM smartvakt_device_report;

Reference: https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

Upvotes: 9

Justin Malinchak
Justin Malinchak

Reputation: 557

SELECT to_char(now(), 'HH24:MI:SS')  hour_minute_second

Upvotes: 13

Mureinik
Mureinik

Reputation: 311053

The word timezone is redundant (read: wrong). You just need to give the column's name. E.g.:

db=> select extract(hour from observationtime) from smartvakt_device_report;

 date_part 
-----------
        19
(1 row)

Upvotes: 6

redneb
redneb

Reputation: 23840

The following should work

select extract(hour from observationtime) from smartvakt_device_report

Upvotes: 129

Related Questions