Hello all -

I have a site that is supposed to list a history of weekly reports.
I'm using the WEEK() function to find all distinct weeks in the
recordset. However, one I have my week numbers, there is no obvious,
handy way to convert a week and year to an actual date.

I was looking for something like WEEK_TO_DATE( WEEK, YEAR, WEEKDAY )
or WEEK_START_DATE( WEEK, YEAR) where you could get single date from
knowing a week and a year. Absent such function(s), what's the easiest
syntax to use to convert a week to a date? Given the year $year and
the week $week, something like

SELECT DATE_ADD( '$year-01-01', INTERVAL $week WEEK )

To get the first day of that week?

lawpoop@gmail.com schreef:
> Hello all -
>
> I have a site that is supposed to list a history of weekly reports.
> I'm using the WEEK() function to find all distinct weeks in the
> recordset. However, one I have my week numbers, there is no obvious,
> handy way to convert a week and year to an actual date.
>
> I was looking for something like WEEK_TO_DATE( WEEK, YEAR, WEEKDAY )
> or WEEK_START_DATE( WEEK, YEAR) where you could get single date from
> knowing a week and a year. Absent such function(s), what's the easiest
> syntax to use to convert a week to a date? Given the year $year and
> the week $week, something like
>
> SELECT DATE_ADD( '$year-01-01', INTERVAL $week WEEK )
>
> To get the first day of that week?


select DATE_ADD(DATE_ADD('2008-01-01', INTERVAL $week week),
INTERVAL -WEEKDAY('2008-01-01') DAY);

this should return the first day of the week, depending on $week




mysql> SELECT DATE_ADD(DATE_ADD('2008-01-01', INTERVAL 14 week),
INTERVAL -WEEKDAY('2008-01-01') DAY);
+-----------------------------------------------------------------------------------------+
| DATE_ADD(DATE_ADD('2008-01-01', INTERVAL 14 week), INTERVAL
-WEEKDAY('2008-01-01') DAY) |
+-----------------------------------------------------------------------------------------+
| 2008-04-07
|
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(DATE_ADD('2008-01-01', INTERVAL 15 week),
INTERVAL -WEEKDAY('2008-01-01') DAY);
+-----------------------------------------------------------------------------------------+
| DATE_ADD(DATE_ADD('2008-01-01', INTERVAL 15 week), INTERVAL
-WEEKDAY('2008-01-01') DAY) |
+-----------------------------------------------------------------------------------------+
| 2008-04-14
|
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(DATE_ADD('2008-01-01', INTERVAL 16 week),
INTERVAL -WEEKDAY('2008-01-01') DAY);
+-----------------------------------------------------------------------------------------+
| DATE_ADD(DATE_ADD('2008-01-01', INTERVAL 16 week), INTERVAL
-WEEKDAY('2008-01-01') DAY) |
+-----------------------------------------------------------------------------------------+
| 2008-04-21
|
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



--
Luuk

On Apr 15, 1:27 pm, lawp...@gmail.com wrote:
> Given the year $year and the week $week, something like
>
> SELECT DATE_ADD( '$year-01-01', INTERVAL $week WEEK )
>
> To get the first day of that week?

After some testing, I found that the syntax above does not give
desired results.

For instance
SELECT DATE_ADD( '2008-01-01', INTERVAL 1 WEEK )

Returns
2008-01-08

It simply added 7 days, or one week, to the date. Since Jan 1 '08 is
not the start of the week, but rather a Tuesday, I got back the date
of Tuesday of week 1, 2008.

On Apr 15, 1:49 pm, Luuk <L...@invalid.lan> wrote:
> this should return the first day of the week, depending on $week
>
> mysql> SELECT DATE_ADD(DATE_ADD('2008-01-01', INTERVAL 14 week),
> INTERVAL -WEEKDAY('2008-01-01') DAY);

Thanks for the input, Luuk. However, the syntax is a little off; it's
not quite the inverse of the week function.

Given
SELECT DISTINCT WEEK( `timestamp`, 1 ) AS `week`, YEAR(`timestamp`)
FROM `table`

to get the week number and the year. Then, using those values in the
week function again:

SELECT
DATE_ADD(
'$year-01-01',
INTERVAL $week WEEK
) AS `date`,
WEEK(
DATE_ADD(
$year-01-01',
INTERVAL $week WEEK
), 1
) AS `week2`


Year | Week | Date | Week2
--------|----------|------------------|------------------
2008 | 16 | 2008-04-22 | 17
2008 | 15 | 2008-04-15 | 16
2008 | 14 | 2008-04-08 | 15
2008 | 13 | 2008-04-01 | 14

So, I'm off by one on the week.

On Apr 15, 2:40 pm, lawp...@gmail.com wrote:
> On Apr 15, 1:49 pm, Luuk <L...@invalid.lan> wrote:
>
> > this should return the first day of the week, depending on $week
>
> > mysql> SELECT DATE_ADD(DATE_ADD('2008-01-01', INTERVAL 14 week),
> > INTERVAL -WEEKDAY('2008-01-01') DAY);
>
> Thanks for the input, Luuk. However, the syntax is a little off; it's
> not quite the inverse of the week function.

Oops! I was testing my own functions, not the on you posted. My
mistake!

On Apr 15, 1:49 pm, Luuk <L...@invalid.lan> wrote:
> select DATE_ADD(DATE_ADD('2008-01-01', INTERVAL $week week),
> INTERVAL -WEEKDAY('2008-01-01') DAY);
>
> this should return the first day of the week, depending on $week

Actually, I am still getting an off-by-one error on this syntax,
also.

SELECT
DISTINCT (
WEEK(
timestamp,
1
)
) AS `week` ,
YEAR( timestamp ) AS `year`
FROM table


SELECT
DATE_ADD(
DATE_ADD(
'" . $year . "-01-01',
INTERVAL $week week
),
INTERVAL - WEEKDAY('" . $year .
"-01-01') DAY
) AS `date`,
WEEK(
DATE_ADD(
DATE_ADD(
'" . $year . "-01-01',
INTERVAL $week week
),
INTERVAL - WEEKDAY('" .
$year . "-01-01') DAY
), 1
) AS `week2`


Year Week Date week2
2008 16 2008-04-21 17
2008 15 2008-04-14 16
2008 14 2008-04-07 15
2008 13 2008-03-31 14
2008 12 2008-03-24 13
2008 11 2008-03-17 12

On Apr 15, 1:49 pm, Luuk <L...@invalid.lan> wrote:
>
> select DATE_ADD(DATE_ADD('2008-01-01', INTERVAL $week week),
> INTERVAL -WEEKDAY('2008-01-01') DAY);
>
> this should return the first day of the week, depending on $week
>

Actually, this function does work as long as you don't change the
start of the week, at least for 2007 and 2008.

lawpoop@gmail.com schreef:
> On Apr 15, 1:27 pm, lawp...@gmail.com wrote:
>> Given the year $year and the week $week, something like
>>
>> SELECT DATE_ADD( '$year-01-01', INTERVAL $week WEEK )
>>
>> To get the first day of that week?
>
> After some testing, I found that the syntax above does not give
> desired results.
>
> For instance
> SELECT DATE_ADD( '2008-01-01', INTERVAL 1 WEEK )
>
> Returns
> 2008-01-08
>
> It simply added 7 days, or one week, to the date. Since Jan 1 '08 is
> not the start of the week, but rather a Tuesday, I got back the date
> of Tuesday of week 1, 2008.

that's why i added "INTERVAL -WEEKDAY('2008-01-01') DAY" to it, to make
it the start of the week.

--
Luuk

lawpoop@gmail.com schreef:
> On Apr 15, 1:49 pm, Luuk <L...@invalid.lan> wrote:
>> select DATE_ADD(DATE_ADD('2008-01-01', INTERVAL $week week),
>> INTERVAL -WEEKDAY('2008-01-01') DAY);
>>
>> this should return the first day of the week, depending on $week
>>
>
> Actually, this function does work as long as you don't change the
> start of the week, at least for 2007 and 2008.

i know no one who will change the start of the week... ;-)

--
Luuk

Luuk:
> lawpoop@gmail.com schreef:

> > Actually, this function does work as long as you don't change the
> > start of the week, at least for 2007 and 2008.
>
> i know no one who will change the start of the week... ;-)

Well, I remember a wild weekend in 1976, when I eventually fell asleep
and started the week on Tuesday.
:-)

--
Erick