SQL to compare hours and minutes when Timestamp in PostgreSql

Navigation
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 one day which one is larger.

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

In this case would be ignored the dates being compared only '10: 00 '> '11: 00 'returning FALSE. I tried to use EXTRACT () but I did not find anything like "time" SQL would be something like this If time existed ): 

SELECT * FROM "Tabela" WHERE EXTRACT(TIME FROM "Coluna_1") > EXTRACT(TIME FROM "Coluna_2")
    
asked by anonymous 12.01.2017 / 00:24

2 answers

3

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

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

Example - sql fiddle

    
12.01.2017 / 00:33
2

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

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

Example

    
12.01.2017 / 00:39
How to add an HTML element to PDF via iTextSharp? Merge two distinct lists using LINQ