生成一系列日期 - 使用日期类型作为输入 [英] Generate series of dates - using date type as input

问题描述

文档 generate_series表示参数可以是 int 或 bigint 用于 generate_series(start, stop) 和 generate_series(start, stop, step) case 和 timestamp 或 timestamp with time zone 用于 generate_series(start, stop, step interval).

generate_series 也以 date 类型作为输入并返回 timestamp with timezone 的原因是什么?

pg=# select generate_series('2014-01-01'::date,'2014-01-02'::date,'1 day');生成系列------------------------2014-01-01 00:00:00+012014-01-02 00:00:00+01(2 行)

解决方案

感谢 函数类型解析我们还可以将date值传递给generate_series() 因为有一个从 date 到 的隐式转换>timestamp 以及从 date 到 timestamptz.会模棱两可，但 timestamptz 在日期/时间类型"中是 首选".详细说明:

• 生成两个日期之间的时间序列在 PostgreSQL 中

对于一个裸 date，本地时间 00:00 在演员表中被假定.请注意，如果您使用 date 作为输入，当前时区设置会直接影响结果，因为显然，2014-01-10 00:00"在 Tokio 中代表的时间点与实际不同在纽约.

Postgres 如何决定可接受的类型?

Postgres 基本上区分了三种类型的转换::>

Explicit casts .. 使用 CAST 或 :: 语法时.
赋值转换 .. 将值分配给目标列时的隐式转换.
隐式转换 .. 在所有其他表达式中的隐式转换.

必须在系统中注册一个从输入类型到预期类型的​​隐式强制转换，以使函数静默接受(并转换)输入值.

要查看定义了哪些类型转换到 timestamptz，您可以查询目录表 pg_cast:

SELECT castsource::regtype, casttarget::regtype, castcontext从 pg_castWHERE casttarget = 'timestamptz'::regtype;铸源|投靶|演员表-----------------------------+--------------------------+--------------禁欲 |带时区的时间戳 |一世日期 |带时区的时间戳 |一世没有时区的时间戳 |带时区的时间戳 |一世带时区的时间戳 |带时区的时间戳 |一世

所有这些转换都是隐式.根据 castcontext 上的文档:

<块引用>

指示可以在哪些上下文中调用强制转换.e 表示仅作为显式转换(使用 CAST 或 :: 语法).a 表示隐含在分配给目标列，以及显式分配.i 表示隐含在表达式中，以及其他情况.

粗体强调我的.

Documentation for generate_series says that argument can be int or bigint for generate_series(start, stop) and generate_series(start, stop, step) cases and timestamp or timestamp with time zone for generate_series(start, stop, step interval).

What is the reason that generate_series works also with date type as input and returns timestamp with timezone?

pg=# select generate_series('2014-01-01'::date,'2014-01-02'::date,'1 day');
    generate_series     
------------------------
 2014-01-01 00:00:00+01
 2014-01-02 00:00:00+01
(2 rows)

解决方案

Thanks to function type resolution we can also pass date values to generate_series() because there is an implicit cast from date to timestamp as well as from date to timestamptz. Would be ambiguous, but timestamptz is "preferred" among "Date/time types". Detailed explanation:

• Generating time series between two dates in PostgreSQL

For a bare date the local time 00:00 is assumed in the cast. Be aware that the current time zone setting directly affects the result if you use date as input since, obviously, '2014-01-10 00:00' represents a different point in time in Tokio than it does in New York.

How does Postgres decide what types are acceptable?

Postgres basically distinguishes between three types of casts:

Explicit casts .. when using CAST or :: syntax.
Assignment cast .. implicit cast when a value is assigned to a target column.
Implicit cast .. implicit casts in all other expressions.

There has to be an implicit cast registered in the system from the input type to the expected type to make a function silently accept (and convert) an input value.

To see which casts are defined to timestamptz, you can query the catalog table pg_cast:

SELECT castsource::regtype, casttarget::regtype, castcontext
FROM   pg_cast
WHERE  casttarget = 'timestamptz'::regtype;

         castsource          |        casttarget        | castcontext
-----------------------------+--------------------------+-------------
 abstime                     | timestamp with time zone | i
 date                        | timestamp with time zone | i
 timestamp without time zone | timestamp with time zone | i
 timestamp with time zone    | timestamp with time zone | i

All of these casts are implicit. Per documentation on castcontext:

Indicates what contexts the cast can be invoked in. e means only as an explicit cast (using CAST or :: syntax). a means implicitly in assignment to a target column, as well as explicitly. i means implicitly in expressions, as well as the other cases.

Bold emphasis mine.

这篇关于生成一系列日期 - 使用日期类型作为输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！