I guess a follow-on question here would be, which function should I be using more often, the datetime() function, or the strftime()? I didn't think that the datetime() function accepted the format argument (%Y) like strftime() did.
On Wed, Sep 29, 2010 at 12:42 PM, J. Bobby Lopez <[email protected]> wrote: > 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
