postgres json parse to timestamp

Question

I am trying to get some data into my postgres DB from a CSV file containing a json dump. As long as it is just strings it is alright, but I want my strings containing timestamps to be stored as timestamps in postgres. Soo I need to do some conversion of the two fields:registerdate and dateofbirth. The below code works except for the date conversion lines...

Any clue on how to successfully convert the two strings to timestamps below:

CREATE TABLE users (
    id SERIAL,
    mongo_id TEXT,
    password VARCHAR(128),
    firstname VARCHAR(200),
    lastname VARCHAR(200),
    dateofbirth TIMESTAMP,
    registerdate TIMESTAMP,
    displayname VARCHAR(200),
    language VARCHAR(200),
    country VARCHAR(200),
    profilepicture VARCHAR(200),
    backgroundpicture VARCHAR(200),
    type VARCHAR(200),
    sex VARCHAR(6),
    offlinemode BOOLEAN,
    email VARCHAR(200),
    friends VARCHAR(255)[]
);

INSERT INTO users (mongo_id, password,firstname,lastname, dateofbirth, registerdate, displayname, language)
SELECT data->>'_id',
 data->>'password',
  data->>'firstName',
   data->>'secondName',
    to_timestamp(data->'dateOfBirth'->>'$date'),   /*<------*/
     to_timestamp(data->'registerDate'->>'$date'), /*<-------*/
      data->>'displayName',
       data->>'language'
FROM import.mongo_users;

The data format in mongo_users:

 { "_id" : "1164", "password" : "aaa123123", "firstName" : "Adam", "secondName" : "Kowlalski", "dateOfBirth" : { "$date" : "2014-05-18T07:41:09.202+0200" }, "registerDate" : { "$date" : "2016-06-01T12:59:53.941+0200" }, "displayName" : "Adam Kowlalski", "language" : "nb", "country" : null, "profilePicture" : null, "backgroundPicture" : null, "type" : "USER", "sex" : "MALE", "offlineMode" : true, "email" : "bk_1164@test.email", "friends" : [ "KUE" ] }

score 13 · Answer 1 · answered Jun 06 '16 at 08:44

The to_timestamp function requries two parameters: date_time in text format, and the formatting template.

You don't need to use to_timestamp since your date-time values are already formatted with a valid timestamp, and PostgreSQL understands json-formatted timestamps well enough. The following works well:

SELECT data->>'_id',
 data->>'password',
  data->>'firstName',
   data->>'secondName',
    (data->'dateOfBirth'->>'$date')::timestamp, --<< simply cast to timestamp
     (data->'registerDate'->>'$date')::timestamp, --<< simply cast to timestamp
      data->>'displayName',
       data->>'language'
FROM (SELECT
 '{ "_id" : "1164", "password" : "aaa123123", "firstName" : "Adam", "secondName" : "Kowlalski", "dateOfBirth" : { "$date" : "2014-05-18T07:41:09.202+0200" },
   "registerDate" : { "$date" : "2016-06-01T12:59:53.941+0200" }, "displayName" : "Adam Kowlalski", "language" : "nb", "country" : null, "profilePicture" : null,
   "backgroundPicture" : null, "type" : "USER", "sex" : "MALE", "offlineMode" : true, "email" : "bk_1164@test.email", "friends" : [ "KUE" ] }'::jsonb as data) d

could be shorter and clearer with smth like ```db=# with j(b) as (values(jsonb '{ "$date" : "2016-06-01T12:59:53.941+0200" }')) select b->>'$date', timestamptz (b->>'$date') from j; ?column? | timestamptz ------------------------------+---------------------------- 2016-06-01T12:59:53.941+0200 | 2016-06-01 10:59:53.941+00 (1 row)``` — Vao Tsun, May 16 '23 at 15:43

rarspace01 · Answer 2 · 2016-06-06T12:33:05.290

6

Your JSON Date format looks like ISO 8601 (https://en.wikipedia.org/wiki/ISO_8601). For transforming the input string to a date variable you should use the to_date function.

e.g. to_date(data->'dateOfBirth'->>'$date','YYYY-MM-DD"T"HH24:MI:SS')

Be ware that you have to check if Timezone differences play a role. Postgresql has an option OF: https://www.postgresql.org/docs/current/static/functions-formatting.html

edited Jun 06 '16 at 12:33

answered Jun 06 '16 at 08:45

rarspace01

399
3
15

1

Note that in this particular case it also contains timezone data (`+0200`), so that template will discard that information (although it may be redundant anyway in this particular case) – Ezequiel Tolnay Jun 06 '16 at 08:47

score 0 · Answer 3 · answered Mar 30 '21 at 07:08

For me this is what worked.

SELECT to_timestamp(nullif(LEFT(dates_json->>'date_prop',10), '')::numeric) as date_extracted FROM table_name

First shrink the value to 10 symbols (if the timestamp include miliseconds), then check if it is null, convert to numeric, then pass it to function to_timestamp(). This way I fixed another error "date/time field value out of range".

postgres json parse to timestamp

3 Answers3