CODEWITHSUNDEEP
postgresqltimestamp
Eric Kim
Eric Kim

Reputation: 2698

PostgreSQL - to_timestamp is not properly converting unix timestamp

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');

Upvotes: 1

Views: 909

Answers (1)

klin
klin

Reputation: 121604

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.

Upvotes: 1

Related Questions