如何在postgresql“json”数据类型中存储用于plv8的日期?

I wanted to use Date.UTC to store dates and datetimes in postgresql 9.2 "json" field, but of course it fails:

我想用日期。在postgresql 9.2“json”字段中存储日期和日期时间的UTC，但是它当然失败了:

hp=> update formapp_record set data='{"dt": Date.UTC(120, 10, 2)}' where id=17;
ERROR:  invalid input syntax for type json
LINE 1: update formapp_record set data='{"dt": Date.UTC(120, 10, 2)}...
                                       ^
DETAIL:  Token "Date" is invalid.
CONTEXT:  JSON data, line 1: {"dt": Date...

It is possible to store the UTC timestamp directly, but then how could the decoder know that the value should decode to a date or datetime instead of an int ?

可以直接存储UTC时间戳，但是解码器如何知道该值应该解码到日期或日期时间而不是int ?

It is also possible to store the Date.UTC call as string as such:

也可以存储日期。以字符串形式调用UTC:

update formapp_record set data='{"dt": "Date.UTC(120, 10, 2)"}' where id=17;

While that works, it requires 0. checking if the string starts with Date.UTC and 1. use eval in plv8

当它工作时，它需要0。检查字符串是否以日期开始。UTC和1。使用eval plv8

A solution would be to store some metadata like:

解决方案是存储一些元数据，如:

update formapp_record set data='{"dt": {"_type": "date", "_value": [120, 10, 2]}}' where id=17;

But that's not very "standard", it's even "hackish".

但那不是很“标准”，甚至是“黑客”。

What's your take on this matter ?

你对这件事有什么看法?

2 个解决方案

#1

A possible solution is to use Postgres's row_to_json function and just store your dates as timestamps and extract them to json as required. However, Tobe Hede wrote some json functions for Postgres that may help and seem to be alot more complete then the 2 native options that Postgres has made available for 9.2

一种可能的解决方案是使用Postgres的row_to_json函数，将日期存储为时间戳，并根据需要将其提取为json。然而，Tobe Hede为Postgres编写了一些json函数，这些函数可能会有所帮助，而且似乎比Postgres为9.2提供的两个本地选项要完整得多

See post How do I query using fields inside the new PostgreSQL JSON datatype? for the thread.

请参见post如何使用新的PostgreSQL JSON数据类型中的字段进行查询?的线程。

#2

Alas, json doesn't know anything about dates.

唉，json对日期一无所知。

I'd store an ISO 8601 date as a string. Yes, it's a pain. Yes, it means there's no nice standard way to tell "this is a date" vs "this is a string". IMO it's less painful than most of the other options, though.

我将ISO 8601日期存储为字符串。是的,这是一个痛苦。是的，这意味着没有标准的方法来判断“这是一个日期”还是“这是一个字符串”。但在我看来，这比其他大多数选择都要少一些痛苦。

#1