SQL to compare hours and minutes when Timestamp in Postgresql

Asked

Viewed 2,208 times

2

I am trying to compare 2 timestamp fields, which have different dates, ignoring the dates and using only the hour/minute simultaneously, to know, within a same day which of them is bigger.

EX: '2000-10-10 10:00:22' > '2000-02-02 11:00:10'

In this case the dates would be ignored by comparing only '10:00' > '11:00' and returning FALSE. I tried to use EXTRACT() but I couldn’t find something like "time" SQL would be something like that (If the team existed):

SELECT * FROM "Tabela" WHERE EXTRACT(TIME FROM "Coluna_1") > EXTRACT(TIME FROM "Coluna_2")
sql postgresql timestamp
  • 1

    SELECT * FROM "Tabela" WHERE "Coluna_1"::time > "Coluna_2"::time works?

  • Thanks for saying, I didn’t know this way. I wanted something to eliminate the hundredths, but this one should work.

2 answers

3


You can apply a date cast to time to compare only hours/minutes, just use :: followed by the type.

SELECT * FROM "Tabela" WHERE "Coluna_1"::time > "Coluna_2"::time

Example - sql fiddle

2

Your approach is correct, except for the use of team. An alternative is to make the CAST to TIMESTAMP field for TIME, allowing comparison only of field time. The use of CAST in this case, in addition to working, maintains compatibility with other Dbms (which have the TIME directive, obviously):

SELECT * FROM TABELA WHERE CAST(Coluna_1 AS TIME) > CAST(Coluna_2 AS TIME)

Example

  • This alternative did not work, at least not in Postgresql. Apparently it does not allow a cast of the timestamp column for a team. Removing the team it provides timestamp epoch, including date.

  • Sorry for the carelessness. I edited the reply. Thank you.

Browser other questions tagged sql postgresql timestamp

You are not signed in. Login or sign up in order to post.