Convert fields to timestamp for comparison

I have a Postres database and want to compare two timestamp fields.

Basic query:

select t1.valu1, t1.valu2 from table1 as t1 where t1.valu1 == t1.valu2

With timestamp comparison:

select t1.valu1, t1.valu2 from table1 as t1
where EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE  t1.valu1 )
  ==  EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE  t1.valu2 ) 

Sample values for valu1:

'14:50:15', '10:50:15'

valu2:

'11:10:15', '17:50:15'

I want to convert valu1 and valu2 to timestamps and compare them afterwards.

开发者_如何学C// syntax of extract
EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '20:38:18');

I get an error in the following format:

EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE  t1.valu2 ) 

---

Your sample values are text representations for time not timestamp:

'14:50:15', '10:50:15', '11:10:15', '17:50:15'

Assuming text as source type according to later comment.

It would make sense to cast them to time to eliminate insignificant white space or leading 0 from the comparison. But for a simple cast, it has to be a valid time format:

SELECT valu1, valu2
FROM   table1
WHERE  valu1::time = valu2::time

If you'd really want to compare "as timestamps", you'd need to use to_timestamp() with a matching format pattern:

SELECT valu1, valu2
FROM   table1
WHERE  to_timestamp(valu1, 'HH24:MI:SS')
     = to_timestamp(valu2, 'HH24:MI:SS');

But this hardly makes any sense ...

---

You have to give, at least, a day, e.g.:

select EXTRACT(EPOCH FROM '2010-07-05 20:38:18'::timestamptz);

gives

 date_part
------------
 1278358698

---

You can just cast them:

select dateintext1::timestamp - dateintext2::timestamp from ...

Note that you can cast to time, date, timestamp, etc. As long as the format in the field is correct.

Far better to convert the text fields to date, time, or timestamp fields.

alter table abc alter column texttime type timestamp using (texttime::timestamp);