Thank you so very much! On Wed, Sep 29, 2010 at 12:39 PM, Richard Hipp <[email protected]> wrote:
> On Wed, Sep 29, 2010 at 12:34 PM, J. Bobby Lopez <[email protected]> wrote: > > > 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! > > > > SELECT DISTINCT strftime('%Y',date) FROM data; > > > > > > Bobby > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > [email protected] > _______________________________________________ > 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
