Re: [sqlite] Group by week

[MikeW]

Moshe Sharon  writes:

> 
> Hi
> 
> How can I select group by week
> 
> moshe

The steps below are an idea - let me know if it works OK.

There is an arithmetic expression known as 'Zeller's congruence'
for giving the day of the week, 0-6 ... look it up.

The last step in the calculation is taking residue modulo 7.

However, if you don't do this, but instead, integer divide by 7,
then results sharing the same quotient should be in the same
week.

If your week starts on a different day-of-week from Zeller,
add the appropriate number of days before division.

Regards,
MikeW



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Group by week

[Brad Dewar]

Something like this seems the most obvious way:

SELECT strftime('%Y%W', date_col) AS w, other_data FROM my_table GROUP
BY w

See http://sqlite.org/lang_datefunc.html for usage.  That doc also
includes some caveats related to precision and locale, etc.

Brad



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Longbotham
Sent: Thursday, February 05, 2009 1:05 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Group by week

Hi Moshe,

  There are probably a number of ways to do this.  One possibility would
be
to store your dates in binary date value within the sqlite database,
which I
believe is so many seconds since some date in 1969.  You could then use
a
constant such as:

#define SecondsInWeek 60*60*24*7

You could then use the starting date also in this format to select where
the
value in the database was greater than the starting day and less than
the
starting day plus SecondInWeek.  To select the next week add
SecondsInWeek
to your starting value and reiterate...

-Tom

On Thu, Feb 5, 2009 at 8:43 AM, John Stanton <jo...@viacognis.com>
wrote:

> You need a function which gives the week number.  Note that this is
> calculated differently in the USA and Europe., so you need to use the
> correct rules to write the function.
>
> Moshe Sharon wrote:
> > Hi
> >
> > How can I select group by week
> >
> > moshe
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Group by week

[Tom Longbotham]

Hi Moshe,

  There are probably a number of ways to do this.  One possibility would be
to store your dates in binary date value within the sqlite database, which I
believe is so many seconds since some date in 1969.  You could then use a
constant such as:

#define SecondsInWeek 60*60*24*7

You could then use the starting date also in this format to select where the
value in the database was greater than the starting day and less than the
starting day plus SecondInWeek.  To select the next week add SecondsInWeek
to your starting value and reiterate...

-Tom

On Thu, Feb 5, 2009 at 8:43 AM, John Stanton <jo...@viacognis.com> wrote:

> You need a function which gives the week number.  Note that this is
> calculated differently in the USA and Europe., so you need to use the
> correct rules to write the function.
>
> Moshe Sharon wrote:
> > Hi
> >
> > How can I select group by week
> >
> > moshe
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Group by week

[John Stanton]

You need a function which gives the week number.  Note that this is 
calculated differently in the USA and Europe., so you need to use the 
correct rules to write the function.

Moshe Sharon wrote:
> Hi
>
> How can I select group by week
>
> moshe
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Group by week

[Martin Engelschalk]

Hi,

perhaps you want to look at 
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

or perhaps you would want to post a little more information about your 
problem

Martin

Moshe Sharon wrote:
> Hi
>
> How can I select group by week
>
> moshe
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Group by week

[Moshe Sharon]

Hi

How can I select group by week

moshe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users