PostgreSQL - to_timestamp is not properly converting unix timestamp

Question

I'm trying to get current UTC time, and insert it into PostgreSQL timestamp. But it's not working properly.

I am using the following command:

INSERT INTO public.rt_block_height 
VALUES(to_timestamp('2018-09-09 00:36:00.778653', 'yyyy-mm-dd hh24:mi:ss.MS.US'), 83.7)

However, when I check the result, it looks like this:

tutorial=# select * from rt_block_height;
          time           | block_height
-------------------------+--------------
 2018-09-09 00:48:58.653 |         83.7
(1 row)

I don't know what's causing this mismatch.

FYI, here my source code for table:

CREATE TABLE IF NOT EXISTS public.rt_BLOCK_HEIGHT
(
"time" timestamp without time zone,
BLOCK_HEIGHT double precision
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.rt_BLOCK_HEIGHT
OWNER to postgres;
SELECT create_hypertable('rt_BLOCK_HEIGHT', 'time');

klin · Accepted Answer · 2018-09-09 01:29:07Z

1

There is a logical error in the format string as you should not use MS and US at the same time. However, you do not need the function at all, just cast the string to timestamp:

INSERT INTO public.rt_block_height 
VALUES('2018-09-09 00:36:00.778653'::timestamp, 83.7)

From the documentation:

to_timestamp and to_date exist to handle input formats that cannot be converted by simple casting. For most standard date/time formats, simply casting the source string to the required data type works, and is much easier.

answered Sep 9, 2018 at 1:29

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL - to_timestamp is not properly converting unix timestamp

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related