HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

Error while inserting into array of timestamps

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
errorwhilearrayintoinsertingtimestamps

Problem

Im using postgres 10.1 and the Datestyle config is the following

DateStyle
 -----------
 ISO, MDY


Im having this error


ERROR: date/time field value out of range: "1535673858" Hint: Perhaps
you need a different "datestyle" setting.

While executing the following insert statement:

INSERT INTO ex (taken_ats) VALUES('{ 1535673858 , 1535678856}')


Schema:

CREATE TABLE ex (
    taken_ats TIMESTAMP WITHOUT TIME ZONE[]
)

Solution

1535673858 is neither in the ISO format, nor in any of the other valid date input formats.

To convert a Unix epoch value into the internal Postgres type, you must use the single-argument to_timestamp() function:

INSERT INTO ex(taken_ats) VALUES(ARRAY[to_timestamp(1535673858), to_timestamp(1535678856)]);

Code Snippets

INSERT INTO ex(taken_ats) VALUES(ARRAY[to_timestamp(1535673858), to_timestamp(1535678856)]);

Context

StackExchange Database Administrators Q#216381, answer score: 3

Revisions (0)

No revisions yet.