postgresql 将 postgres 日期表示转换为 ISO 8601 字符串

Turn postgres date representation into ISO 8601 string

提问by CallMeNorm

I'm trying to format a Postgres date representation into a ISO 8601 string. I'm assuming that there is a Postgres function that can do it, but I found the documentation short on examples.

我正在尝试将 Postgres 日期表示形式格式化为 ISO 8601 字符串。我假设有一个 Postgres 函数可以做到这一点，但我发现文档中的示例很短。

My query is

我的查询是

SELECT
  now()::timestamp

which returns

返回

[{{2016, 8, 9}, {3, 56, 55, 754181}}]

I'm trying to get the date into a format that looks more like 2016-8-9T03:56:55+00:00.

我正在尝试将日期转换为看起来更像 2016-8-9T03:56:55+00:00.

What changes do I need to make to my query to make that happen? Thanks for your help.

我需要对我的查询进行哪些更改才能实现这一点？谢谢你的帮助。

采纳答案by CallMeNorm

I think I found a way to do the formatting, but it's not ideal because I'm writing the formatting myself.

我想我找到了一种格式化的方法，但这并不理想，因为我是自己编写格式的。

Here is a potential solution:

这是一个潜在的解决方案：

SELECT to_char (now()::timestamp at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

回答by Dattaya

Maybe for someone it would be helpful to know that since Postgres 9.4 to_jsonfunction (as well as row_to_json) also converts timestamp to a proper ISO 8601 format but in addition it wraps a value in quotes which might not be desirable:

也许对于某人来说，知道因为 Postgres 9.4to_json函数（以及row_to_json）也将时间戳转换为适当的 ISO 8601 格式会有所帮助，但此外它还将一个值包装在引号中，这可能是不可取的：

SELECT now();
  2017-05-10 15:57:23.736054+03

SELECT to_json(now());
  "2017-05-10T15:57:23.769561+03:00"

-- in case you want to trim the quotes
SELECT trim(both '"' from to_json(now())::text);
  2017-05-10T15:57:23.806563+03:00

回答by ADJenks

This is a terse way to "turn a PostgreSQL date representation into an ISO 8601 string":

这是“将 PostgreSQL 日期表示形式转换为 ISO 8601 字符串”的简洁方法：

SELECT to_json(now())#>>'{}'

It uses the #>>operator in combination with the to_json()function, which can both be found on this page: https://www.postgresql.org/docs/current/functions-json.html

它#>>结合使用运算符和to_json()函数，两者都可以在此页面上找到：https: //www.postgresql.org/docs/current/functions-json.html

The operator "Get[s] JSON object at specified path as text". However when you specify an empty array literal '{}'as the path, it specifies the root object.

运算符“以文本形式获取指定路径中的 [s] JSON 对象”。但是，当您指定一个空数组文字'{}'作为路径时，它指定了根对象。

Compare this method to similar methods:

将此方法与类似方法进行比较：

SELECT
to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF') AS most_lengthy, -- See note: *
trim(both '"' from to_json(now())::text) AS a_bit_lengthy,
to_json(now())::text AS unwanted_quotes,
to_json(now())#>>'{}' AS just_right

It's shorter but produces the same results.

它更短，但产生相同的结果。

* Also, JavaScript will not parse the first method's output via the Date()constructor, because it expects a simplification of the ISO 8601 which only accepts time zones in (+/-)HH:mmor Zformat, but OFreturns (+/-)HHformat without the minutes, UNLESS the input timezone is a fraction of an hour, e.g. using SET timezone=-4.5;at the beginning of the session. Alternatively you could manually append your timezone as a string to the lengthy version and exclude the OF

* 此外，JavaScript 不会通过Date()构造函数解析第一个方法的输出，因为它期望ISO 8601的简化，它只接受(+/-)HH:mm或Z格式的时区，但OF返回(+/-)HH格式不带分钟，除非输入时区是一个小时的一小部分，例如SET timezone=-4.5;在会话开始时使用。或者，您可以手动将时区作为字符串附加到冗长的版本中并排除OF

回答by Hugh

Set the timezonesession variable to whatever timezone you want the output to be in, then use to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF')

timezone将会话变量设置为您希望输出所在的任何时区，然后使用to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF')

If you use at time zone '...'be aware that this will strip off any timezone information, and assume that the user already knows the timezone.

如果您使用，请at time zone '...'注意这将剥离任何时区信息，并假设用户已经知道时区。

If you use at time zone 'UTC'then the output should always be the UTC time, with correct time zone information (no offset).

如果使用，at time zone 'UTC'则输出应始终为 UTC 时间，并带有正确的时区信息（无偏移）。

set timezone='UTC';


select to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF');

2017-11-17T02:02:26+00  /* UTC time */


select to_char(now() at time zone 'Australia/Sydney', 'YYYY-MM-DD"T"HH24:MI:SSOF');

2017-11-17T13:02:26+00  /* Local Sydney time, but note timezone is incorrect. */


set timezone='Australia/Sydney';


select to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF');

2017-11-17T13:02:26+11  /* Local Sydney time with correct time zone! */


select to_char(now() at time zone 'Australia/Sydney', 'YYYY-MM-DD"T"HH24:MI:SSOF');

2017-11-17T13:02:26+00  /* Still local Sydney time, but time zone info has been removed. */


select to_char(now() at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSOF');

2017-11-17T02:02:26+00  /* Correct UTC time with correct offset. */

This blog postgives quite a detailed explanation.

这篇博文给出了相当详细的解释。

回答by rofrol

Only function worked for me because you need to set timezone.

只有功能对我有用，因为您需要设置时区。

To have default value timezone with zone:

要具有带区域的默认值时区：

create table somedata (
  release_date timestamptz DEFAULT NOW()
)

Create function:

创建函数：

CREATE OR REPLACE FUNCTION date_display_tz(param_dt timestamp with time zone)
 RETURNS text AS
$$
DECLARE var_result varchar;
BEGIN
PERFORM set_config('timezone', 'UTC', true);
var_result := to_char(param_dt , 'YYYY-MM-DD"T"HH24:MI:SS:MS"Z"');
RETURN var_result;
END;
$$ language plpgsql VOLATILE;

And output:

和输出：

# SELECT
#   localtimestamp, current_timestamp,
#   to_char(localtimestamp, 'YYYY-MM-DD"T"HH24:MI:SS:MS"Z"'),
#   to_char(current_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS:MS"Z"'),
#   date_display_tz(localtimestamp), date_display_tz(current_timestamp);
         timestamp          |              now              |         to_char          |         to_char          |     date_display_tz      |     date_display_tz
----------------------------+-------------------------------+--------------------------+--------------------------+--------------------------+--------------------------
 2017-04-27 23:48:03.802764 | 2017-04-27 21:48:03.802764+00 | 2017-04-27T23:48:03:802Z | 2017-04-27T23:48:03:802Z | 2017-04-27T21:48:03:802Z | 2017-04-27T21:48:03:802Z
(1 row)

Look at thisalso:

也看看这个：

If you want the server to return time zone information respective of another time zone, I believe you'll need to use SET TIME ZONE. Otherwise, the server automatically (converts the timestamp) and returns the time zone of the server.

如果您希望服务器返回另一个时区的时区信息，我相信您需要使用 SET TIME ZONE。否则，服务器自动（转换时间戳）并返回服务器的时区。

test=# select (current_timestamp at time zone 'UTC') at time zone 'UTC';
            timezone
-------------------------------
  2005-04-22 16:26:57.209082+09
(1 row)

test=# set time zone 'UTC';
SET
test=# select (current_timestamp at time zone 'UTC') at time zone 'UTC';
            timezone
-------------------------------
  2005-04-22 07:27:55.841596+00
(1 row)

test=# select (current_timestamp at time zone 'UTC');
           timezone
----------------------------
  2005-04-22 07:28:48.888154
(1 row)

test=# select (current_timestamp at time zone 'UTC')::timestamptz;
            timezone
-------------------------------
  2005-04-22 07:38:19.979511+00
(1 row)

• http://sql-info.de/postgresql/notes/to_date-to_timestamp-gotchas.html

• http://sql-info.de/postgresql/notes/to_date-to_timestamp-gotchas.html