How to extract only the hour from a timestamp in sql?

Question

Hi I have data with column name 'trx_time'.

The data looks like this: '2022-03-06 11:25:36'

I just want to extract the hour from the string. How can I do it? I was thinking regex_extract. But don't know how to write the regex.

Answer 1

This will extract the Hour from a date field in Presto.

SELECT extract(HOUR FROM trx_time) as hour FROM table_name;

However, if your field is a String type representing a timestamp (ISO 8601), you would have to use the from_iso8601_date or from_iso8601_timestamp functions.

SELECT extract(HOUR FROM from_iso8601_date(trx_time)) as hour FROM table_name;