On Wed, Sep 29, 2010 at 9:34 AM, 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%';
>
This might work for you
select distinct strftime('%Y', date) from data;
(Assuming your table is named "data", and the column with the time
stamp is called "date")
Stephan
> 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
>
--
Stephan Wehner
-> http://stephan.sugarmotor.org (blog and homepage)
-> http://loggingit.com
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
-> http://twitter.com/stephanwehner / @stephanwehner
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
