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

Identifiers?

It's unclear whether you are using upper-case letters in your identifiers, which requires double-quoting.

Also, Day is a reserved word in SQL and - even though allowed as identifier in Postgres - may require special treatment. I would generally avoid reserved words and basic type names. Double-quoting takes care of it in any case. My standing advice is to use legal, lower-case identifiers that never require double-quoting. See:

• Are PostgreSQL column names case-sensitive?

date and time types

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

SELECT EXTRACT(EPOCH FROM ("Day" + "Time"));

You can just add the types date and time to get a timestamp [without time zone].
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 columns of type text / varchar , 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 (note the double-quotes):

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

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.
Use one of the first two syntax variants.

Answer 2

SELECT EXTRACT(EPOCH FROM (Day || ' ' || Time)::timestamp);

Answer 3

This works fine for me :

SELECT CONCAT(Day,' ',Time)::timestamp;