Cast date to timestamp in pgSQL

Question

Is there a way to cast a date to timestamp. For an example if I had a date like 2012/05/01, how can I convert it to a timestamp like 2012-05-01 00:00:01

Answer 1

You can convert it to a timestamp with this code:

SELECT current_date::timestamp

It will directly assign the time 00:00:00 to current_date.

Answer 2

You can cast the date column to text and then concatenate with the time portion of the timestamp. In the query below I create a timestamp from the current date.

select (cast(current_date as text) || ' 00:00:01'):: timestamp
from yourTable;

Or if we already have a date type, we can simply add on the time component:

select current_date + '00:00:01'::time

Output:

11.07.2017 00:00:01

Demo

Update:

If you just want the difference in months between two dates you can use the following:

DATE_PART('month', AGE(end_date, start_date))

Of course, there is no time component involved here, but assuming you were planning to assign the dummy 00:00:01 to both timestamps, the result would not change.

Answer 3

select cast(current_date as timestamp) + interval '1 second'

Close to Standard SQL, only the interval syntax differs interval '1' second.