lundi 29 juin 2015

SQL Query and filtering data

I am using a Time Zone database (http://ift.tt/1eUkB4W) and I'm struggling with a SQL query.

The validity of a time zone is depends on time_start field in the database. This is important to get the correct time_start.

Below are the data

zone_id|zone_name          |time_start
391    |America/Los_Angeles|2147397247
391    |America/Los_Angeles|1425808800
391    |America/Los_Angeles|2140678800
391    |America/Los_Angeles|9972000

392    |America/Metlakatla |2147397247
392    |America/Metlakatla |436352400
392    |America/Metlakatla |9972000

393    |America/Anchorage  |2147397247
393    |America/Anchorage  |2140682400
393    |America/Anchorage  |2120122800
393    |America/Anchorage  |1425812400
393    |America/Anchorage  |9979200

The example below shows how to query the time zone information using zone name America/Los_Angeles.

SELECT * FROM timezone 
WHERE time_start < strftime('%s', 'now')
AND zone_name='America/Los_Angeles'
ORDER BY time_start DESC LIMIT 1;

This query returns

391|America/Los_Angeles|1425808800

I'd like to to do the same thing but for all zone_id with one SQL Query.

The expected results

391|America/Los_Angeles|1425808800
392|America/Metlakatla |436352400
393|America/Anchorage  |1425812400

SQL Fiddle

Aucun commentaire:

Enregistrer un commentaire