将Postgres中的JSON解析为时间戳

Question

将Postgres中的JSON解析为时间戳

4

我正在尝试从包含JSON转储的CSV文件将一些数据存入我的Postgres数据库。只要它是字符串就可以，但我希望我的包含时间戳的字符串作为时间戳存储在Postgres中。因此，我需要对registerdate和dateofbirth两个字段进行一些转换。以下代码有效，除了日期转换行...有没有什么线索可以成功地将下面的两个字符串转换为时间戳：

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;

在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" ] }

- David Karlsson

3个回答

6

您的JSON日期格式看起来像ISO 8601(https://en.wikipedia.org/wiki/ISO_8601)。为了将输入字符串转换为日期变量，您应该使用to_date函数。

例如：to_date(data->'dateOfBirth'->>'$date','YYYY-MM-DD"T"HH24:MI:SS') 请注意，您必须检查时区差异是否起作用。Postgresql有一个选项OF:https://www.postgresql.org/docs/current/static/functions-formatting.html

- rarspace01

1

请注意，在这种特殊情况下，它还包含时区数据（+0200），因此该模板将丢弃该信息（尽管在这种特定情况下可能是多余的）。 - Ezequiel Tolnay

0

对我来说，这是有效的方法。

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

首先将值缩小为10个符号（如果时间戳包括毫秒），然后检查它是否为空，转换为数字，然后传递给函数to_timestamp()。这样我修复了另一个错误“日期/时间字段值超出范围”。

- vencedor

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ezequiel Tolnay · Accepted Answer

to_timestamp函数需要两个参数：文本格式的日期时间和格式化模板。

由于您的日期时间值已经使用有效的时间戳进行格式化，并且PostgreSQL足够了解json格式的时间戳，因此您不需要使用to_timestamp函数。以下内容效果很好：

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