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
