0

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.

  • 1
    Compose a proper timestamp out of the two, like `timestamp '2010-12-12' + time '10:30' – mustaccio May 2 at 20:31
  • 1
    What was the reason for not storing these as one timestamp? – Lennart May 3 at 5:34
  • The csv I was using to insert rows to the db has them as separate rows. I could just add the code in my python script to combine them, but I was seeing if there was a workaround. And a_horse_with_no_name provided the answer to that. – cesium133 May 3 at 20:05
1

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.

| improve this answer | |
  • Great solution that doesn't require that I create a new column that concatenates date and time. – cesium133 May 2 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.