Postgresql – Querying the most recent records with two fields: datestamp and timestamp

datetimepostgresqlquerytimestamp

I have a table called airquality that has records for air quality measurements taken on different days and times within those days. These date/timestamps are stored in two different columns: validdate and validtime. I can query either one of those fields to return me the maximum value with the following query:

select * 
from airquality
where validdate = (select max(validdate) from airquality)

This returns me the correct rows for the most recent day (format: 05/02/20). But how can I modify this query to also check for the latest time in the validtime column (format: 16:00)?

I tried the following:

select * 
from airquality
where validdate = (select max(validdate) from airquality)
and validtime = (select max(validtime) from airquality)

But this doesn't work because I can have timestamps for 05/01/2020 that have a timestamp that 05/02/2020 doesn't have yet. (Example: 05/02/20 16:00 vs 05/01/20 23:00)

I am on Postgres 12.2

Any suggestions or ideas?
Thanks in advance.

Best Answer

Create a proper timestamp from the two columns:

select * 
from airquality
where validdate + validtime  = (select max(validdate + validtime ) from airquality)

This assumes that validdate is corrected defined with the data type date and validtime is correct defined as a time column.

Related Question