postgres json parse to timestamp
<p>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... </p>

<p>Any clue on how to successfully convert the two strings to timestamps below:</p>

<pre><code>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-&gt;&gt;'_id',
 data-&gt;&gt;'password',
  data-&gt;&gt;'firstName',
   data-&gt;&gt;'secondName',
    to_timestamp(data-&gt;'dateOfBirth'-&gt;&gt;'$date'),   /*&lt;------*/
     to_timestamp(data-&gt;'registerDate'-&gt;&gt;'$date'), /*&lt;-------*/
      data-&gt;&gt;'displayName',
       data-&gt;&gt;'language'
FROM import.mongo_users;
</code></pre>

<p>The data format in mongo_users:</p>

<pre><code> { "_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" ] } 
</code></pre>