当我想按日期筛选时，ISO8601是PostgreSQL jsonb的最佳日期格式吗? [英] Is ISO8601 the best date-format for PostgreSQL jsonb when i want to filter by the date?

问题描述

我是PostgreSQL的新手，我有以下问题:

我有一个只有id列和data-column的表，它使用jsonb-type.在jsonb-object内部，我有一个datetime字段.我读过很多文章，我应该使用ISO-8601日期格式存储在数据库中.

我想按日期按如下方式过滤表格:

SELECT * FROM table WHERE data->'date' > '2016-01-01T00:00'

这真的是最好的日期格式吗?

先谢谢您了:)

解决方案

该格式的最大优点是字符串顺序与日期顺序相对应，因此像您在问题中引用的顺序这样的比较实际上可以按预期进行. /p>

第二个优点是该格式的时间戳可以轻松转换为PostgreSQL timestamp with time zone值，因为类型输入函数可以理解该格式.

我希望您不要在基督之前"处理日期，因为这对那些日期来说不太容易.

I'm new to PostgreSQL and I have the following question:

I have a table with just an id-column and a data-column, which uses the jsonb-type. Inside the jsonb-object I have a datetime field. I read in various posts, that I should use the ISO-8601 dateformat to store in the DB.

I want to filter my table by date like this:

SELECT * FROM table WHERE data->'date' > '2016-01-01T00:00'

Is this really the best date-format for this purpose?

Thanks in advance :)

解决方案

The big advantage of that format is that string order corresponds to date order, so a comparison like the one you quote in your question would actually work as intended.

A second advantage is that a timestamp in that format can easily be converted to a PostgreSQL timestamp with time zone value, because the type input function understands this format.

I hope you are not dealing with dates “before Christ”, because it wouldn't work so easily with those.

这篇关于当我想按日期筛选时，ISO8601是PostgreSQL jsonb的最佳日期格式吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！