CODEWITHSUNDEEP
postgresql
hikari sakunami
hikari sakunami

Reputation: 35

date and time concatenation in postgres

I wanted to get the only the hour of the time and concatenate it with the date. here's my query

SELECT distinct TOTALIZER_METER_READINGS.date + to_char(TOTALIZER_METER_READINGS.time ,'HH')
FROM TOTALIZER_METER_READINGS

is there any other way to get the hour of the time without turning it into text?

Upvotes: 1

Views: 6503

Answers (2)

user330315
user330315

Reputation:

+ is the operator to add numbers, dates or intervals.

The string concatenation operator in SQL is ||.

As you are storing date and time in two columns rather then using a single timestamp column, I would convert them to a single timestamp value, then apply to_char() on the "complete" timestamp:

Adding a time to a date returns a timestamp that can then be formatted as you want: 

SELECT distinct to_char(TOTALIZER_METER_READINGS.date + TOTALIZER_METER_READINGS.time, 'yyyy-mm-dd HH')
FROM TOTALIZER_METER_READINGS

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 247625

You can use EXTRACT (or the date_part function):

SELECT EXTRACT(hour FROM current_timestamp);

The result type is double precision.

Upvotes: 2

Related Questions