英文:
When to use DATE_TRUNC() vs. DATE_PART()?
问题
我不知道何时在查询中使用 DATE_TRUNC 和 DATE_PART()。
我没有尝试过太多，只是做了一些网页搜索，但我对它们并不完全了解，因为我刚开始学习SQL（Postgres）。
英文:
I do not know when to use DATE_TRUNC and DATE_PART() in a query.
I have not really tried much, just some web searches that I do not fully grasp but I just started learning SQL (Postgres).
答案1
得分: 2
它们都执行非常不同的功能。其中一个将日期截断到指定的精度（有点像四舍五入），而另一个仅返回日期时间的特定部分。
从文档中得知：
date_part()：
date_part函数是基于传统的Ingres等价SQL标准函数extract而建模的：
date_part('field', source)
请注意，这里的field参数需要是一个字符串值，而不是一个名称。 date_part的有效字段名称与extract相同。出于历史原因，date_part函数返回double precision类型的值。这可能会导致某些用途的精度丧失。建议使用extract代替。
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
结果：16
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
结果：4
date_trunct()：
函数date_trunc在概念上类似于数字的截断函数。
date_trunc(field, source [, time_zone ])中，source是一个时间戳，带有时区的时间戳或间隔的值表达式。日期和时间类型的值将自动转换为时间戳或间隔。field选择要截断输入值的精度。返回值同样是时间戳、带时区的时间戳或间隔类型，它将所有比所选精度低的字段设置为零（或对于天和月设置为一）。
...
示例（假设本地时区是美国/纽约）：
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
结果：2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
结果：2001-01-01 00:00:00
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
结果：2001-02-16 00:00:00-05
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
结果：2001-02-16 08:00:00-05
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
结果：3 days 02:00:00
英文:
They both do very different things. One truncates a date to the precision specified (kind of like rounding, in a way) and the other just returns a particular part of a datetime.
From the documentation:
date_part():
> The date_part function is modeled on the traditional Ingres equivalent
> to the SQL-standard function extract:
date_part('field', source)
> Note that here the field parameter needs to be a string value, not a
> name. The valid field names for date_part are the same as for extract.
> For historical reasons, the date_part function returns values of type
> double precision. This can result in a loss of precision in certain
> uses. Using extract is recommended instead.
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Result: 4
date_trunct():
> The function date_trunc is conceptually similar to the trunc function
> for numbers.
>
> date_trunc(field, source [, time_zone ]) source is a value expression
> of type timestamp, timestamp with time zone, or interval. (Values of
> type date and time are cast automatically to timestamp or interval,
> respectively.) field selects to which precision to truncate the input
> value. The return value is likewise of type timestamp, timestamp with
> time zone, or interval, and it has all fields that are less
> significant than the selected one set to zero (or one, for day and
> month).
...
> Examples (assuming the local time zone is America/New_York):
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
Result: 2001-02-16 00:00:00-05
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
Result: 2001-02-16 08:00:00-05
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:00
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论