PostgreSQL: Convert to timestamp

Question

I have the following SQL Server Script to do in the PostgreSQL.

SQL Server Script:

convert(datetime,convert(varchar,JoinTiming,108))

My Try:

to_timestamp(to_char(JoinTiming,'HH24:MI:SS'))

Error:

ERROR:  function to_timestamp(text) does not exist

What is the type of JoinTiming?

Clodoaldo Neto
– Clodoaldo Neto

2014-07-24 10:17:15 +00:00
Commented Jul 24, 2014 at 10:17 — Clodoaldo Neto
– Clodoaldo Neto, Commented Jul 24, 2014 at 10:17
@Clodoaldo Neto, Its of type datetime in SQL Server.

Sarfaraz Makandar
– Sarfaraz Makandar

2014-07-24 10:24:54 +00:00
Commented Jul 24, 2014 at 10:24 — Sarfaraz Makandar
– Sarfaraz Makandar, Commented Jul 24, 2014 at 10:24

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-07-24 11:06:42Z

5

PostgreSQL has a "time" data type.

select current_timestamp::time(0)  -- or
select cast(current_timestamp as time(0))

now
time without time zone
--
06:51:58

In PostgreSQL, you can't cast time to timestamp.

Timestamps contain a date as well as a time--which date should PostgreSQL use? There's no way to know. So PostgreSQL simply doesn't allow that kind of cast.

You can add values of type "time" to dates and to timestamps. So expressions like these will work.

select date '2001-01-01'  + '13:43'::time(0)
select '2001-01-01'::date  + '13:43'::time(0)
select (current_timestamp + '13:43'::time(0))::timestamp(0)

Also, the standard SQL CAST() works like you'd expect.

select cast(current_timestamp as time(0))

answered Jul 24, 2014 at 11:06

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Sherrill 'Cat Recall' Over a year ago

@Meem: You're welcome. I edited my answer to remove trailing milliseconds, which more closely matches what SQL Server's convert(varchar,...,108) does.

Collectives™ on Stack Overflow

PostgreSQL: Convert to timestamp

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related