82

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
Improve this question
2
  • 1
    What is the exact type of observationtime? Commented Sep 5, 2016 at 16:51
  • observationtime: timestamp without time zone, Commented Sep 5, 2016 at 16:52

4 Answers 4

Reset to default
133

The following should work

select extract(hour from observationtime) from smartvakt_device_report
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Ahh Thanks alot :)
is there any way to get hours in hh format? It returns 0 instead I want it as 00
14 
SELECT to_char(now(), 'HH24:MI:SS') hour_minute_second
Improve this answer

1 Comment

Please add a description to your answer that exactly what your code does.
11

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

Improve this answer

Comments

7

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)
Improve this answer

Comments