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
