CODEWITHSUNDEEP
sqlpostgresqltimestamp
i am batman
i am batman

Reputation: 679

Cast date to timestamp in pgSQL

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

Upvotes: 24

Views: 79060

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522571

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.

Upvotes: 13

dnoeth
dnoeth

Reputation: 60502

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

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

Upvotes: 3

mrina713
mrina713

Reputation: 527

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.

Upvotes: 30

Oto Shavadze
Oto Shavadze

Reputation: 42853

You can add time part to date

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

Upvotes: 0

Related Questions