如何在 PostgreSQL 中获取星期开始和结束日期字符串? [英] How to get week start and end date string in PostgreSQL?
问题描述
我正在使用 PostgreSQL 8.3.我有一张这样的桌子:
I am using PostgreSQL 8.3. I have a table like this:
id regist_time result
-----------------------------------
1 2012-07-09 15:00:08 3
2 2012-07-25 22:24:22 7
4 2012-07-07 22:24:22 8
regist_time
的数据类型是timestamp
.
我需要找到一个星期的时间间隔(从开始到结束)和 sum(result) 为 num.
I need to find a week time interval(start to end) and sum(result) as num.
我想得到如下结果:
week num
---------------------------------
7/1/2012-7/7/2012 10
7/8/2012-7/14/2012 5
7/15/2012-7/21/2012 3
7/22/2012-7/28/2012 11
我可以得到今年的周数:
I can get the week number just in this year:
SELECT id,regis_time, EXTRACT(WEEK FROM regis_time) AS regweek
FROM tba
关键部分是
EXTRACT(WEEK FROM regis_time)
提取函数只能获取今年的周数,如何获取一周内的开始时间到结束时间?
extract function can only get the week number in this year, how can I get start time to end time in one week?
推荐答案
你可以使用date_trunc('week', ...)
.
例如:
SELECT date_trunc('week', '2012-07-25 22:24:22'::timestamp);
-> 2012-07-23 00:00:00
然后,如果您对开始时间不感兴趣,可以将其转换为日期.
Then, you can convert this into a date, if you're not interested in a start time.
也获取结束日期:
SELECT date_trunc('week', '2012-07-25 22:24:22'::timestamp)::date
|| ' '
|| (date_trunc('week', '2012-07-25 22:24:22'::timestamp)+ '6 days'::interval)::date;
-> 2012-07-23 2012-07-29
(我这里使用了默认格式,你当然可以修改为使用 MM/DD/YYYY.)
(I've used the default formatting here, you can of course adapt this to use MM/DD/YYYY.)
请注意,如果您想比较时间戳,而不是使用 (date_trunc('week', ...) + '6 days'::interval
,您可能需要添加整整一周,并在一周结束时使用严格的比较.
Note that, if you want to make comparisons on timestamps, instead of using (date_trunc('week', ...) + '6 days'::interval
, you might want to add an entire week and use a strict comparison for the end of the week.
这将排除一周最后一天的 y
时间戳(因为截止时间是当天的午夜).
This will exclude y
timestamps on the last day of the week (since the cut-off time is midnight on the day).
date_trunc('week', x)::date <= y::timestamp
AND y::timestamp <= (date_trunc('week', x) + '6 days'::interval)::date
这将包括他们:
date_trunc('week', x)::date <= y::timestamp
AND y::timestamp < (date_trunc('week', x) + '1 week'::interval)
(在极少数情况下,您不能直接在 y
上使用 date_trunc
.)
(That's in the rare cases when you can't use date_trunc
on y
directly.)
如果您的一周从星期日开始,请将 date_trunc('week', x)::date
替换为 date_trunc('week', x + '1 day'::interval)::date - '1 day'::interval
应该可以工作.
If your week starts on a Sunday, replacing date_trunc('week', x)::date
with date_trunc('week', x + '1 day'::interval)::date - '1 day'::interval
should work.
这篇关于如何在 PostgreSQL 中获取星期开始和结束日期字符串?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!