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

问题描述

的

文档说，该参数可以是int或bigint用于generate_series(start, stop)和generate_series(start, stop, step)情况，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');
    generate_series     
------------------------
 2014-01-01 00:00:00+01
 2014-01-02 00:00:00+01
(2 rows)

解决方案

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

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

对于裸露的date，本地时间00:00被假定为演员表.请注意，如果使用date作为输入，当前时区设置将直接影响结果，因为很明显，"2014-01-10 00:00"在Tokio中表示的时间与在纽约时不同. /p>

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

Postgres基本上区分了三种类型的演员:

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

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

要查看为 timestamptz定义了哪些强制类型转换，可以查询目录表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屋！