I am importing a csv file into a PostgreSQL database. Most of the file imports without any trouble until I get this message

ERROR:  date/time field value out of range: "1421088300"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY accidents, line 6356158, column datetime: "1421088300"

But this seems to be a valid timestamp for 01/12/2015 @ 6:45pm (UTC) according to https://www.unixtimestamp.com/index.php

So why is it out of range?

  • This is a timestamp in a particular sense of the word. However, it looks like a string literal with the quotes. You might need an intermediate table to import the data into, which accepts text, and then convert it to a proper timestamp.
    – dezso
    Sep 13, 2017 at 16:29
  • stackoverflow.com/q/37342829
    – dnoeth
    Sep 13, 2017 at 17:28

1 Answer 1


Seconds since epoch timestamps

Your "timestamp" is in seconds-since-epoch. Per dezso, use to_timestamp(). I missed that when I checked \dfS+

Bad complex idea, and dragons.

Check the input formats. They cover casts from strings. That's what COPY is doing under the hood. The only method that even remotely looks like a long number is ISO 8601. If you look at that example though you'll see it's not a seconds-since-epoch

Example    | Description
19990108   | ISO 8601; January 8, 1999 in any mode

This is basically the same as another example on that chart.

Example    | Description
1999-01-08 | ISO 8601; January 8, 1999 in any mode

Converting to timestamp with abstime as an intermediary format

So if you want to convert from seconds-since-epoch, you can cheat by using the internal abstime since there is no available cast directly to timestamp from a string of seconds-since-epoch.

SELECT 1421088300::abstime::timestamp;
 2015-01-12 12:45:00
(1 row)

What's happening here is that abstime is binary coercable with integer. You can see that in \dC+. I checked \dfS+ for functions to get from integer to timestamp and found none. There is a cast though from integer to abstime (which is stored as an integer), and from abstime to timestamp.

If this is a new table you could actually type that column as abstime. It should load perfectly fine. And then you can ALTER TABLE. here is an example, except I'm not running COPY (but it should work all the same).


TABLE foo;
 2015-01-12 12:45:00-06
(1 row)

  ALTER bar
  TYPE timestamp;

TABLE foo;
 2015-01-12 12:45:00
(1 row)

\d foo;
                Table "public.foo"
 Column |            Type             | Modifiers 
 bar    | timestamp without time zone | 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.