yeah, total brain failure on my part. Should have been strftime('%Y',date).
Sorry.
--- On Wed, 9/29/10, J. Bobby Lopez <[email protected]> wrote:
> From: J. Bobby Lopez <[email protected]>
> Subject: Re: [sqlite] Getting unique years from a timestamp column
> To: "General Discussion of SQLite Database" <[email protected]>
> Date: Wednesday, September 29, 2010, 12:42 PM
> This one doesn't seem to return the
> desired result, instead it returned a
> blank line?
>
> On Wed, Sep 29, 2010 at 12:40 PM, David Bicking <[email protected]>
> wrote:
>
> > How about trying
> >
> > select distinct datetime(date,'%Y') as year from
> data;
> >
> > David
> >
> > --- On Wed, 9/29/10, J. Bobby Lopez <[email protected]>
> wrote:
> >
> > > From: J. Bobby Lopez <[email protected]>
> > > Subject: [sqlite] Getting unique years from a
> timestamp column
> > > To: [email protected]
> > > Date: Wednesday, September 29, 2010, 12:34 PM
> > > Hello,
> > >
> > > I'm looking to do something completely in SQLite,
> without
> > > the assistance of
> > > Perl.
> > >
> > > The problem is this:
> > >
> > > I have a table with a text column, and the column
> contains
> > > unix timestamps.
> > > I would like to get a list of the unique years
> from that
> > > timestamp column.
> > >
> > > Here's what the raw dataset looks like:
> > >
> > > 1|blopez|somekinda.log|2010-07-10
> > > 13:21:10|10.100.0.1|make_db.pl
> > > |usage_reporter()
> > > 2|blopez|somekinda.log|2010-09-28
> > > 06:18:51|10.100.0.1|make_db.pl
> > > |usage_reporter()
> > > 3|blopez|somekinda.log|2010-06-28
> > > 17:58:37|10.100.0.1|make_db.pl
> > > |usage_reporter()
> > > 4|blopez|somekinda.log|2011-06-28
> > > 17:58:37|10.100.0.1|make_db.pl
> > > |usage_reporter()
> > >
> > > What I'd like to do is write a query that would
> return
> > > "2010" and "2011",
> > > the two unique years in the listing.
> > >
> > > It's pretty easy to get all records which match a
> single
> > > year, for example:
> > >
> > > SELECT id FROM data WHERE datetime(date) LIKE
> '2010%';
> > >
> > > I'm sure I could use a BEGIN/COMMIT block and
> test for each
> > > year
> > > individually, but I don't want to hard-code the
> year that
> > > I'm looking for,
> > > if you get my meaning.
> > >
> > > Any assistance on this would be
> appreciated. Thanks!
> > >
> > > Bobby
> > > _______________________________________________
> > > sqlite-users mailing list
> > > [email protected]
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [email protected]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
