postgresql - Get a timestamp from concatenating day and time columns

Question

I am having day and time fields in database. I want to get the time-stamp by concatenating the day and time. How to do this in PostgreSQL?

I have done this:

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28');

And it is working fine.

But when I tried replacing day and time fields I am getting the following error:

SELECT EXTRACT(EPOCH FROM TIMESTAMP Day || ' ' || Time);

ERROR:  syntax error at or near "Day"
LINE 1: ...quest_count > 0 AND (EXTRACT(EPOCH FROM TIMESTAMP Day || ' ' || Time)) > (e...

Answer 1

date and time types

If your Day is of type date and your Time is of type time, there is a very simple solution:

SELECT EXTRACT(EPOCH FROM (day + time));

You can just add date and time to get a timestamp [without time zone] (which is interpreted according to the time zone setting of your session).

And, strictly speaking, extracting the epoch is unrelated to your question per se.
date + time result in a timestamp, that's it.

String types

If you are talking about string literals or text / varchar columns, use:

SELECT EXTRACT(EPOCH FROM ('2013-07-18' || ' ' || '21:52:12')::timestamp);

or

SELECT EXTRACT(EPOCH FROM cast('2013-07-18' ||' '|| '21:52:12' AS timestamp));

Your form does not work

SELECT EXTRACT(EPOCH FROM TIMESTAMP ('2013-07-18' || ' ' || '21:52:12'));

This would work:

SELECT EXTRACT(EPOCH FROM "timestamp" ('2013-07-18' || ' ' || '21:52:12'));

I quote the manual about type casts:

It is also possible to specify a type cast using a function-like syntax:

typename ( expression )

However, this only works for types whose names are also valid as function names. For example, double precision cannot be used this way, but the equivalent float8 can. Also, the names interval, time, and timestamp can only be used in this fashion if they are double-quoted, because of syntactic conflicts. Therefore, the use of the function-like cast syntax leads to inconsistencies and should probably be avoided.

Bold emphasis mine.
The gist of it: rather use one of the first two syntax variants.