如何在PostgreSQL中调整SET intervalstyle（更改Interval输出）？ [英] How to tweak the SET intervalstyle (change the Interval Output) in PostgreSQL?

问题描述

我已经阅读了此在线PostgreSQL文档... http://www.postgresql.org/docs/9.4/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE
要点8.5.5如何调整默认的间隔输出。 。我的意思是默认间隔显示如下...

I have read in this online PostgreSQL documentation... http://www.postgresql.org/docs/9.4/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE in the point 8.5.5 something about how to tweak the default Interval Output. . I mean the default interval is shown like this...

00:00:00.000 (if the timedifference is lower than a day or month or year)

1 day 00:00:00.000 (if the timedifference reaches days, but is lower than a month or a year)

1 month 1 day 00:00:00.000 (if the timediffence reaches months, but is lower than a year)

1 year 1 month 1 day 00:00:00.000 (if it reaches years, months, days)

it evens uses plurarl cases (years, mons, days) when their values are greater than one.

在选择（查询）此间隔值（作为文本）时，所有其他变体使其他任何应用程序都很难将其转换为适当的时间。因此，我希望postgresql始终显示年，月，n天，即使它们的值是0（如果可以像这样显示间隔的日期部分也可能会更好... 01-11-30，将零添加到值小于十时为左侧）

All these variations make difficult to any other app when SELECTing (query) this interval values (as text) to convert it to a proper time. So I would like postgresql to always show year, month n days, even if their value are 0 (it could be even better if it could show the date part of the interval like this... 01-11-30, adding zeros to the left side when values are less than ten)

我知道我可以使用to_char（）将间隔更改为文本，但我真的想避免这种情况，就像一些优秀的Postgresql程序员程序员告诉我的那样，是否确实有一种方法可以调整间隔输出，如Postgresql文档中所述。

I know I can change the interval to text, using to_char() but I really would like to avoid that, I would like some good fellow postgresql programmer to tell me if it is true that there is a way to tweak the Interval Output as is said in the postgresql documentation.

感谢Advanced。

Thanks Advanced.

PD：关于该主题的另外两个链接
https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/SET/SETDATESTYLE.htm
http：// my .vertica.com / docs / 6.1.x / HTML / index .htm＃13874.htm

PD: two more links about the subject https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/SET/SETDATESTYLE.htm http://my.vertica.com/docs/6.1.x/HTML/index.htm#13874.htm

推荐答案

您可以设置间隔输出样式，但只能设置为以下几种预定义格式，这些格式在输入时无歧义，并且PostgreSQL知道如何解析回间隔。根据文档，这些是SQL标准间隔格式，PostgreSQL特定语法的两个变体以及iso_8601间隔。

You can set the interval output style, but only to one of a few pre-defined formats that are unambigious on input, and that PostgreSQL knows how to parse back into intervals. Per the documentation these are the SQL standard interval format, two variants of PostgreSQL specific syntax, and iso_8601 intervals.

如果您想要熟悉且易于解析的内容，请考虑使用：

If you want something familiar and easy to parse, consider using:

SET intervalstyle = 'iso_8601'

，并以ISO1601间隔使用现成的解析器。

and using an off-the-shelf parser for ISO1601 intervals.

这篇关于如何在PostgreSQL中调整SET intervalstyle（更改Interval输出）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！