Convert a string representing a timestamp to an actual timestamp in PostgreSQL?

Question

In PostgreSQL: I convert string to timestamp with to_timestamp():

select * from ms_secondaryhealthcarearea
where to_timestamp((COALESCE(update_datetime, '19900101010101'),'YYYYMMDDHH24MISS') 
    > to_timestamp('20121128191843','YYYYMMDDHH24MISS')

But I get this error:

ERROR:  syntax error at end of input
LINE 1: ...H24MISS') >to_timestamp('20121128191843','YYYYMMDDHH24MISS')
                                                                       ^
********** Error **********

ERROR: syntax error at end of input
SQL state: 42601
Character: 176

Why? How to convert a string to timestamp?

score 8 · Accepted Answer · answered Nov 28 '12 at 13:02

One too many opening brackets. Try this:

select * 
from ms_secondaryhealthcarearea 
where to_timestamp(COALESCE(update_datetime, '19900101010101'),'YYYYMMDDHH24MISS') >to_timestamp('20121128191843','YYYYMMDDHH24MISS')

You had two opening brackets at to_timestamp:

where to_timestamp((COA.. -- <-- the second one  is not needed!

score 6 · Answer 2 · edited May 23 '17 at 12:06

6

@ppeterka has pointed out the syntax error.

The more pressing question is: Why store timestamp data as string to begin with? If your circumstances allow, consider converting the column to its proper type:

ALTER TABLE ms_secondaryhealthcarearea
ALTER COLUMN update_datetime TYPE timestamp
USING to_timestamp(update_datetime,'YYYYMMDDHH24MISS');

Or use timestamptz - depending on your requirements.

edited May 23 '17 at 12:06

Community

1
1

answered Nov 28 '12 at 13:34

Erwin Brandstetter

605,456
145
1,078
1,228

I didn't even realize this ugly issue lying behind the whole story - seemingly just another case of a simple syntax issue. Storing date as any kind of string is a definite no-no... – ppeterka Nov 28 '12 at 13:39

score 1 · Answer 3 · answered Jun 25 '17 at 20:30

1

Another way to convert a string to a timestamp type of PostgreSql is the above,

SELECT to_timestamp('23-11-1986 06:30:00', 'DD-MM-YYYY hh24:mi:ss')::timestamp without time zone;

answered Jun 25 '17 at 20:30

Georgios Syngouroglou

18,813
9
90
92

Christiaan Westerbeek · Answer 4 · 2018-06-08T13:25:23.553

I had the same requirement as how I read the title. How to convert an epoch timestamp as text to a real timestamp. In my case I extracted one from a json object. So I ended up with a timestamp as text with milliseconds

'1528446110978' (GMT: Friday, June 8, 2018 8:21:50.978 AM)

This is what I tried. Just the latter (ts_ok_with_ms) is exactly right.

SELECT
  data->>'expiration' AS expiration,
  pg_typeof(data->>'expiration'),
  -- to_timestamp(data->>'expiration'), < ERROR: function to_timestamp(text) does not exist
  to_timestamp(
    (data->>'expiration')::int8
  ) AS ts_wrong,
  to_timestamp(
    LEFT(
      data->>'expiration',
      10
    )::int8
  ) AS ts_ok,
  to_timestamp(
    LEFT(
      data->>'expiration',
      10
    )::int8
  ) + (
    CASE
      WHEN LENGTH(data->>'expiration') = 13
      THEN RIGHT(data->>'expiration', 3) ELSE '0'
    END||' ms')::interval AS ts_ok_with_ms
FROM (
  SELECT '{"expiration": 1528446110978}'::json AS data
) dummy

This is the (transposed) record that is returned:

expiration    1528446110978
pg_typeof     text
ts_wrong      50404-07-12 12:09:37.999872+00
ts_ok         2018-06-08 08:21:50+00
ts_ok_with_ms 2018-06-08 08:21:50.978+00

I'm sure I overlooked a simpler version of how to get from a timestamp string in a json object to a real timestamp with ms (ts_ok_with_ms), but I hope this helps nonetheless.

Update: Here's a function for your convenience.

CREATE OR REPLACE FUNCTION data.timestamp_from_text(ts text)
RETURNS timestamptz
LANGUAGE SQL AS
$$
  SELECT to_timestamp(LEFT(ts, 10)::int8) +
  (
    CASE
      WHEN LENGTH(ts) = 13
      THEN RIGHT(ts, 3) ELSE '0'
    END||' ms'
  )::interval
$$;

Convert a string representing a timestamp to an actual timestamp in PostgreSQL?

4 Answers4