Menu
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's data type is timestamp.
I need to find a week time interval(start to end) and sum(result) as num.
I want to get the result as:
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 The key part is
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?
418
Use the DATE_PART() function to retrieve the week number from a date in a PostgreSQL database. This function takes two arguments. The first argument is the date part to retrieve; we use 'week', which returns the week number (e.g. “1” for the first week in January, the first week of the year).
In PostgreSQL you can use the extract() function to get the day from a date. You can also use date_part() to do the same thing. When extracting the day from a date, you need to specify what sense of the word “day” you mean. For example, “day of week”, “day of month”, “day of year”, etc.
Postgresql date to string yyymmdd In Postgresql, dates can be converted in specific string formats like yyymmdd, where yyyy for a year, mm for a month, and dd for the date. For that conversion, we will use the to_char function to format the date as a string. Syntax: TO_CHAR(date_value, string_format);
You can use date_trunc('week', ...).
For example:
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.
To get the end date too:
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 (I've used the default formatting here, you can of course adapt this to use MM/DD/YYYY.)
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.
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 This will include them:
date_trunc('week', x)::date <= y::timestamp AND y::timestamp < (date_trunc('week', x) + '1 week'::interval) (That's in the rare cases when you can't use date_trunc on y directly.)
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.
141
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
