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
