Skip to content

A centralized repository for hosting standardized SQL for a SQL based, geo-metadata DB. Utilizing ISO and UN formatting methodologies. Click for more information.

Notifications You must be signed in to change notification settings

quadrimegistus/Metadata-SQL

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

21 Commits
 
 
 
 
 
 

Repository files navigation

Metadata-SQL

A centralized repository for hosting standardized SQL, SQL Creates, SQL Inserts, SQL Updates and source CSV files, for a Metadata DB, written in a versioned manner for Flyway support.

Currently has:

  • Continents
  • Regions (UN-M49)
  • Countries (ISO-3166-1-Alpha-2, ISO-3166-1-Alpha-3, ISO-3166-1 Numeric Code AKA UN-M49 Code)
  • Currencies (ISO-4217)
  • Timezones with Zone ID's (IANA-2019)
  • Units of Measure and Conversions for Temperature

In Progress:

  • states / cities / counties / townships (ISO 3166 subdivisions)

To Do Items:

  • zip/postal codes
  • languages
  • dial codes
  • us tax brackets
  • units of measure and conversions (mass/weight/speed/time/length/data/etc)
  • payment frequency
  • calendar information
  • anything else that might be thought of as pertinent at any point down the line

Querying Timezone & Zone IDs

The validity of a time zone depends on time_start field in the database. This is important to get the correct GMT offset especially areas having Daylight Saving Time. The example below showing how to query the time zone information using zone name America/Los_Angeles.

Input query example:

SELECT z.country_code, z.zone_name, tz.abbreviation, tz.gmt_offset, tz.dst
FROM `timezone` tz JOIN `zone` z
ON tz.zone_id=z.zone_id
WHERE tz.time_start <= UNIX_TIMESTAMP(UTC_TIMESTAMP()) AND z.zone_name='America/Los_Angeles'
ORDER BY tz.time_start DESC LIMIT 1;

Output of above example:

"country_code","zone_name","abbreviation","gmt_offset","dst"
"US","America/Los_Angeles","PST","-28800","0"

In order to show the local time of a zone, please use the following example query:

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(UTC_TIMESTAMP()) + tz.gmt_offset, '%a, %d %b %Y, %H:%i:%s') AS local_time
FROM `timezone` tz JOIN `zone` z
ON tz.zone_id=z.zone_id
WHERE tz.time_start <= UNIX_TIMESTAMP(UTC_TIMESTAMP()) AND z.zone_name='America/Los_Angeles'
ORDER BY tz.time_start DESC LIMIT 1;

Output of above example:

"local_time"
"Tue, 28 Jan 2020, 12:34:44"

Reference Links for Data Sources:

http://www.geopostcodes.com/
http://www.geonames.org/data-sources.html
https://unstats.un.org/unsd/methodology/m49/overview
https://www.iso.org/iso-4217-currency-codes.html
https://www.iso.org/iso-3166-country-codes.html
https://datahub.io/core/language-codes
https://www.loc.gov/standards/iso639-2/php/code_list.php
https://datahub.io/core/fips-10-4
https://www.census.gov/geographies/reference-files/2018/demo/popest/2018-fips.html
https://github.com/timshadel/subdivision-list

Informational Reading on Standardization Organizations:

https://en.wikipedia.org/wiki/Internet_Engineering_Task_Force
https://en.wikipedia.org/wiki/International_Organization_for_Standardization
https://en.wikipedia.org/wiki/Internet_Assigned_Numbers_Authority
https://en.wikipedia.org/wiki/Federal_Information_Processing_Standards

Informational Reading on Specific Standards & Data Aggregates:

http://efele.net/maps/fips-10/data/
https://en.wikipedia.org/wiki/ISO_3166
https://en.wikipedia.org/wiki/ISO_3166-1
https://en.wikipedia.org/wiki/ISO_3166-1_alpha-2
https://en.wikipedia.org/wiki/ISO_3166-1_alpha-3
https://en.wikipedia.org/wiki/ISO_3166-1_numeric
https://en.wikipedia.org/wiki/ISO_3166-2
https://en.wikipedia.org/wiki/ISO_3166-3
https://en.wikipedia.org/wiki/ISO_639
https://en.wikipedia.org/wiki/ISO_639-1
https://en.wikipedia.org/wiki/ISO_639-2
https://en.wikipedia.org/wiki/ISO_639-3
https://en.wikipedia.org/wiki/ISO_639-5
https://en.wikipedia.org/wiki/ISO_639-6
https://en.wikipedia.org/wiki/ISO_4217
https://en.wikipedia.org/wiki/Tz_database

About

A centralized repository for hosting standardized SQL for a SQL based, geo-metadata DB. Utilizing ISO and UN formatting methodologies. Click for more information.

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published