CODEWITHSUNDEEP
jsonpostgresqltimestamp
David Karlsson
David Karlsson

Reputation: 9696

postgres json parse to timestamp

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" : "[email protected]", "friends" : [ "KUE" ] }

Upvotes: 6

Views: 30196

Answers (3)

vencedor
vencedor

Reputation: 711

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".

Upvotes: 1

rarspace01
rarspace01

Reputation: 419

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

Upvotes: 7

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

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" : "[email protected]", "friends" : [ "KUE" ] }'::jsonb as data) d

Upvotes: 16

Related Questions