Simon Willison's Weblog: timezones

Storing times for human events

2024-11-27T20:45:25+00:00

I've worked on various event websites in the past, and one of the unintuitively difficult problems that inevitably comes up is the best way to store the time that an event is happening. Based on that past experience, here's my current recommendation.

This is the expanded version of a comment I posted on lobste.rs a few days ago, which ended up attracting a bunch of attention on Twitter.

The problem

An event happens on a date, at a time. The precise details of that time are very important: if you tell people to show up to your event at 7pm and it turns out they should have arrived at 6pm they'll miss an hour of the event!

Some of the worst bugs an events website can have are the ones that result in human beings traveling to a place at a time and finding that the event they came for is not happening at the time they expected.

So how do you store the time of an event?

The "best practice" that isn't

Any time you talk to database engineers about dates and times you're likely to get the same advice: store everything in UTC. Dates and times are complicated enough that the only unambiguous way to store them is in UTC - no daylight savings or timezones to worry about, it records the exact moment since the dawn of the universe at which the event will take place.

Then, when you display those times to users, you can convert them to that user's current timezone - neatly available these days using the Intl.DateTimeFormat().resolvedOptions().timeZone browser API.

There's a variant of this advice which you're more likely to hear from the PostgreSQL faithful: use TIMESTAMP WITH TIME ZONE or its convenient alias timestamptz. This stores the exact value in UTC and sounds like it might store the timezone too... but it doesn't! All that's stored is that UTC value, converted from whatever timezone was active or specified when the value was inserted.

In either case, we are losing critical information about when that event is going to happen.

Things that can go wrong

What's wrong with calculating the exact UTC time the event is starting and storing only that?

The problem is that we are losing crucial details about the event creator's original intent.

If I arrange an evening meetup for next year on December 3rd at 6pm, I mean 6pm local time, by whatever definition of local time is active on that particular date.

There are a number of ways this time can end up misinterpreted:

User error: the user created the event with an incorrect timezone
User error: the user created the event in the wrong location, and later needs to fix it
International timezone shenanigans: the location in which the event is happening changes its timezone rules at some point between the event being created and the event taking place

User error

By far the most common issue here is user error with respect to how the event was initially created.

Maybe you asked the user to select the timezone as part of the event creation process. This is not a particularly great question: most users don't particularly care about timezones, or may not understand and respect them to the same extent as professional software developers.

If they pick the wrong timezone we risk showing the wrong time to anyone else who views their event later on.

My bigger concern is around location. Imagine a user creates their event in Springfield, Massachusetts... and then a few days later comes back and corrects the location to Springfield, Illinois.

That means the event is happening in a different timezone. If the user fails to update the time of the event to match the new location, we're going to end up with an incorrect time stored in our database.

International timezone shenanigans

One of my favourite niche corners of the internet is the tz@iana.org mailing list. This is where the maintainers of the incredible open source tz database hang out and keep track of global changes to timezone rules.

It's easy to underestimate how much work this is, and how weird these rule changes can be. Here's a recent email proposing a brand new timezone: Antarctica/Concordia:

Goodmorning. I'm writing here to propose a new time zone for an all-year open Antarctic base. The base is a French–Italian research facility that was built 3,233 m (10,607 ft) above sea level at a location called Dome C on the Antarctic Plateau, Antarctica. https://en.wikipedia.org/wiki/Concordia_Station

The timezone is UTC+8 without DST.

That's a pretty easy one. Here's a much more complicated example from March 2023: Lebanon DST change internally disputed:

Lebanon is going through many internal disputes surrounding the latest decision to delay DST. Many institutions are refusing to comply with the change and are going to adopt regular DST on Sunday Mar 26th. Those institutions include but are not limited to:

News agencies

Religious organizations

Schools, universities, etc...

The refusal is mainly centered the legality of that decision and, obviously, the technical chaos it will create because of its short notice. Moreover, as some of the below articles mention, this is also causing sectarian strife.

Lebanon ended up with more than one timezone active at the same time, depending on which institution you were talking to!

It's surprisingly common for countries to make decisions about DST with very little notice. Turkey and Russia and Chile and Morocco are four more examples of countries that can often cause short-term chaos for software developers in this way.

If you've stored your event start times using UTC this is a big problem: the new DST rules mean that an already-existing event that starts at 6pm may now start at 5pm or 7pm local time, according to the UTC time you've stored in your database.

Microsoft Exchange and the DST update of 2007

Via fanf on Lobsters I heard about a fascinating example of this problem in action. In 2005 the Bush administration passed the Energy Policy Act of 2005, one part of which updated the rules for when DST would start across most of the USA.

This resulted in a bug where Microsoft Exchange and Outlook would display appointment times incorrectly! From Exchange Server and Daylight Saving Time (DST) 2007:

After installing the DST updates, all old recurring and single instance appointments that occur during the delta period between the DST 2007 rules and the previous DST rules will be one hour later. These appointments will need to be updated so that they will display correctly in Outlook and Outlook Web Access, and for CDO based applications.

Microsoft released a special "Exchange Calendar Update Tool" executable for people to run to fix all of those upcoming calendar events.

My recommendation: store the user's intent time and the location/timezone

My strong recommendation here is that the most important thing to record is the original user's intent. If they said the event is happening at 6pm, store that! Make sure that when they go to edit their event later they see the same editable time that they entered when they first created it.

In addition to that, try to get the most accurate possible indication of the timezone in which that event is occurring.

For most events I would argue that the best version of this is the exact location of the venue itself.

Users may find timezones confusing, but they hopefully understand the importance of helping their attendees know where exactly the event is taking place.

If you have the venue location you can almost certainly derive the timezone from it. I say almost because, as with anything involving time, there are going to be edge-cases - most critically for venues that are exactly on the line that divides one timezone from another.

I haven't sat down to design my ideal UI for this, but I can imagine something which makes it abundantly clear to the user exactly where and when the event is taking place at that crucial local scale.

Now that we've precisely captured the user's intent and the event location (and through it the exact timezone) we can denormalize: figure out the UTC time of that event and store that as well.

This UTC version can be used for all sorts of purposes: sorting events by time, figuring out what's happening now/next, displaying the event to other users with its time converted to their local timezone.

But when the user goes to edit their event, we can show them exactly what they told us originally. When the user edits the location of their event we can maintain that original time, potentially confirming with the user if they want to modify that time based on the new location.

And if some legislature somewhere on earth makes a surprising change to their DST rules, we can identify all of the events that are affected by that change and update that denormalized UTC time accordingly.

Timezone UIs suck, generally

As an aside, here's my least favorite time-related UI on the modern internet, from Google Calendar:

There isn't even a search option! Good luck finding America/New_York in there, assuming you knew that's what you were looking for in the first place.

Tags: databases, events, time, timezones

California Clock Change

2024-11-03T05:11:06+00:00

California Clock Change

The clocks go back in California tonight and I finally built my dream application for helping me remember if I get an hour extra of sleep or not, using a Claude Artifact. Here's the transcript.

This is one of my favorite examples yet of the kind of tiny low stakes utilities I'm building with Claude Artifacts because the friction involved in churning out a working application has dropped almost to zero.

(I added another feature: it now includes a note of what time my Dog thinks it is if the clocks have recently changed.)

Tags: projects, timezones, ai, llms, ai-assisted-programming, claude-artifacts, prompt-to-app

Australia/Lord_Howe is the weirdest timezone

2024-10-31T22:03:13+00:00

Australia/Lord_Howe is the weirdest timezone

Lord Howe Island - part of Australia, population 382 - is unique in that the island's standard time zone is UTC+10:30 but is UTC+11 when daylight saving time applies. It's the only time zone where DST represents a 30 minute offset.

Via lobste.rs

Tags: timezones

sqlite-jiff

2024-07-23T03:53:52+00:00

sqlite-jiff

I linked to the brand new Jiff datetime library yesterday. Alex Garcia has already used it for an experimental SQLite extension providing a timezone-aware jiff_duration() function - a useful new capability since SQLite's built in date functions don't handle timezones at all.

select jiff_duration(
  '2024-11-02T01:59:59[America/Los_Angeles]',
  '2024-11-02T02:00:01[America/New_York]',
  'minutes'
) as result; -- returns 179.966

The implementation is 65 lines of Rust.

Via @agarcia_me

Tags: sqlite, timezones, rust, alex-garcia

Jiff

2024-07-22T04:48:35+00:00

Jiff

Andrew Gallant (aka BurntSushi) implemented regex for Rust and built the fabulous ripgrep, so it's worth paying attention to their new projects.

Jiff is a brand new datetime library for Rust which focuses on "providing high level datetime primitives that are difficult to misuse and have reasonable performance". The API design is heavily inspired by the Temporal proposal for JavaScript.

The core type provided by Jiff is Zoned, best imagine as a 96-bit integer nanosecond time since the Unix each combined with a geographic region timezone and a civil/local calendar date and clock time.

The documentation is comprehensive and a fascinating read if you're interested in API design and timezones.

Via @burntsushi5

Tags: api-design, timezones, rust, andrew-gallant

It's Time For A Change: datetime.utcnow() Is Now Deprecated

2023-11-18T19:24:33+00:00

It's Time For A Change: datetime.utcnow() Is Now Deprecated

Miguel Grinberg explains the deprecation of datetime.utcnow() and utcfromtimestamp() in Python 3.12, since they return naive datetime objects which cause all sorts of follow-on problems.

The replacement idiom is datetime.datetime.now(datetime.timezone.utc)

Via @miguelgrinberg

Tags: python, timezones

Benjamin "Zags" Zagorsky: Handling Timezones in Python

2022-05-26T03:40:05+00:00

Benjamin "Zags" Zagorsky: Handling Timezones in Python

The talks from PyCon US have started appearing on YouTube. I found this one really useful for shoring up my Python timezone knowledge: It reminds that if your code calls datetime.now(), datetime.utcnow() or date.today(), you have timezone bugs—you’ve been working with ambiguous representations of instances in time that could span a 26 hour interval from UTC-12 to UTC+14. date.today() represents a 24 hour period and hence is prone to timezone surprises as well. My code has a lot of timezone bugs!

Tags: pycon, python, timezones

Temporal: getting started with JavaScript’s new date time API

2021-07-07T22:29:49+00:00

Temporal: getting started with JavaScript’s new date time API

Axel Rauschmayer explores the new proposed API for handling dates, times and timezones in JavaScript., which is under development by Ecma TC39 at the moment (and made available as a Polyfill which you are recommended not to run in production since the API is still being figured out). This is a notoriously difficult problem so it’s always interesting to see the latest thinking on how to best address it.

Tags: datetime, javascript, timezones

Big tech warns of 'Japan's millennium bug' ahead of Akihito's abdication

2018-07-28T14:04:39+00:00

Big tech warns of 'Japan's millennium bug' ahead of Akihito's abdication

Emperor Akihito’s abdication in April 2019 triggers a new era, and the Japanese calendar counts years from the coronation of the current emperor. The era hasn’t changed since 1989 and a great deal of software is unable to handle a change. To make things more complicated... the name of the new era will be announced in late February, but it needs to be represented in unicode as a single new character... and the next version of Unicode (v12) is due out in early March. There may have to be a Unicode 12.1 released shortly afterwards that includes the new codepoint.

Via The tz mailing list

Tags: timezones, unicode, japan

Building a location to time zone API with SpatiaLite, OpenStreetMap and Datasette

2017-12-12T15:52:20+00:00

Given a latitude and longitude, how can we tell what time zone that point lies within? Here’s how I built a simple JSON API to answer that question, using a combination of data from OpenStreetMap, the SpatiaLite extension for SQLite and my Datasette API tool.

This article is out of date and no longer relevant. It has been replaced by a new tutorial: Building a location to time zone API with SpatiaLite, on the official Datasette website.

The API

You can try the API out here: feed it a latitude and longitude and it will return the corresponding time zone ID: https://timezones.datasette.io/timezones/by_point

Some examples:

Brighton, England is in Europe/London (in JSON)
San Francisco, USA is in America/Los_Angeles (in JSON)
Tokyo, Japan is Asia/Tokyo (in JSON)

The data

I was first introduced to Eric Muller’s tz_world shapefile by Nick Williams at Eventbrite, who used it to build us an internal time zone lookup API on top of MySQL’s geospatial data types. Eric’s project is no longer updated and he recommends Evan Siroky’s timezone-boundary-builder project as an alternative, which derives time zone shapes from OpenStreetMap and makes the resulting data available under the Open Database License. The shapefile itself can be downloaded from the GitHub releases page for the project.

Loading the data into SpatiaLite

SpatiaLite is a powerful open source extension for SQLite, which adds a comprehensive suite of geospatial functions - including the ability to ingest shapefiles, convert them into geometries and run point within queries against the resulting shapes.

The easiest way to get it running on OS X is via Homebrew:

$ brew install spatialite-tools
$ brew install gdal

Having installed SpatiaLite, we can ingest the shapefile using .loadshp combined_shapefile timezones CP1252 23032 - here’s the full process, from downloading the shapefile to ingesting it into a new SQLite database file called timezones.db:

$ wget https://github.com/evansiroky/timezone-boundary-builder/releases/download/2017c/timezones.shapefile.zip
$ unzip timezones.shapefile.zip
$ cd dist
$ spatialite timezones.db
SpatiaLite version ..: 4.3.0a   Supported Extensions:
...
Enter SQL statements terminated with a ";"
spatialite> .loadshp combined_shapefile timezones CP1252 23032
========
Loading shapefile at 'combined_shapefile' into SQLite table 'timezones'

BEGIN;
CREATE TABLE "timezones" (
"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"tzid" TEXT);
SELECT AddGeometryColumn('timezones', 'Geometry', 23032, 'MULTIPOLYGON', 'XY');
COMMIT;

Inserted 414 rows into 'timezones' from SHAPEFILE
========
spatialite>

Let’s try it out with a query. Here’s the SQL needed to find the time zone for a point in Tokyo:

select tzid
from
    timezones
where
    within(GeomFromText('POINT(139.7819661 35.6631424)'), timezones.Geometry);

Let’s run that in SpatiaLite and see what we get:

spatialite> select tzid
   ...> from
   ...>     timezones
   ...> where
   ...>     within(GeomFromText('POINT(139.7819661 35.6631424)'), timezones.Geometry);
Asia/Tokyo

Looks good so far! How long is it taking though? We can find out by running .timer on in the spatialite prompt:

spatialite> .timer on
spatialite> select tzid
   ...> from
   ...>     timezones
   ...> where
   ...>     within(GeomFromText('POINT(139.7819661 35.6631424)'), timezones.Geometry);
Asia/Tokyo
CPU Time: user 0.108479 sys 0.064778

That’s a tenth of a second, or 100ms. Fast, but not brilliant.

Speeding it up with a geospatial index

It turns out SpatiaLite includes support for spatial indices, based on SQLite’s R*Tree module. R-Tree indexes can massively accelerate boundary box searches. Our searches are a lot more complex than that, acting as they do against extremely complex polygon shapes - but we can use a boundary box search to dramatically reduce the candidates we need to consider. Let’s create an index against our Geometry column:

SELECT CreateSpatialIndex('timezones', 'geometry');

To take advantage of this index, we need to expand our original SQL to first filter by geometries where their bounding box contains the point we are searching for. SpatiaLite has created an index table called idx_timezones_Geometry against which we can run an R-Tree optimized query. Here’s the SQL we will use:

select tzid
from
    timezones
where
    within(GeomFromText('POINT(139.7819661 35.6631424)'), timezones.Geometry)
    and rowid in (
        SELECT pkid FROM idx_timezones_Geometry
        where xmin < 139.7819661
        and xmax > 139.7819661
        and ymin < 35.6631424
        and ymax > 35.6631424
    );

How does this affect our performance?

spatialite> select tzid
   ...> from
   ...>     timezones
   ...> where
   ...>     within(GeomFromText('POINT(139.7819661 35.6631424)'), timezones.Geometry)
   ...>     and rowid in (
   ...>         SELECT pkid FROM idx_timezones_Geometry
   ...>         where xmin < 139.7819661
   ...>         and xmax > 139.7819661
   ...>         and ymin < 35.6631424
   ...>         and ymax > 35.6631424
   ...>     );
Asia/Tokyo
CPU Time: user 0.001541 sys 0.000111

From 100ms down to 1.5ms - nearly a 70x speedup! Not bad at all.

Building and publishing the API with Datasette

Now that we have a fast SQL query for finding a time zone for a latitude and longitude we can use Datasette to turn it into a JSON API.

The simplest way to do that looks like this:

datasette timezones.db \
    --load-extension=/usr/local/lib/mod_spatialite.dylib

This will start Datasette on port 8001 and load the SpatiaLite extension. You can then navigate to localhost:8001/timezones in your browser and paste in the SQL query… which should look something like this:

https://timezones.datasette.io/timezones?sql=select+tzid%0D%0Afrom%0D%0Atimezones%0D%0Awhere%0D%0Awithin(GeomFromText(‘POINT(139.7819661+35.6631424)’)%2C+timezones.Geometry)%0D%0Aand+rowid+in+(%0D%0A++++SELECT+pkid+FROM+idx_timezones_Geometry%0D%0Awhere+xmin+<+139.7819661%0D%0Aand+xmax+>+139.7819661%0D%0Aand+ymin+<+35.6631424%0D%0Aand+ymax+>+35.6631424%0D%0A++++)%3B

This works (click the JSON link to get the result as JSON) but it’s a little inconvenient to use: you have to construct a URL with the same latitude and longitude repeated in multiple places.

We can improve things using Datasette’s support for SQLite named parameters. Here’s that same SQL query using parameters instead of hard-coded latitude and longitude points:

select tzid
from
    timezones
where
    within(GeomFromText('POINT(' || :longitude || ' ' || :latitude || ')'), timezones.Geometry)
    and rowid in (
        SELECT pkid FROM idx_timezones_Geometry
        where xmin < :longitude
        and xmax > :longitude
        and ymin < :latitude
        and ymax > :latitude)

If you paste this into Datasette it will detect the named parameters and turn them into URL querystring parameters hooked up (in thu UI) to HTML form fields.

To save us from having to include the full SQL in the URL every time we call our new API, let’s take advantage of a new feature introduced in Datasette 0.14: canned queries. These are named, pre-packaged queries that can be defined in a metadata.json file. The file looks like this:

{
    "title": "OpenStreetMap Time Zone Boundaries",
    "license": "ODbL",
    "license_url": "http://opendatacommons.org/licenses/odbl/",
    "source": "timezone-boundary-builder",
    "source_url": "https://github.com/evansiroky/timezone-boundary-builder",
    "databases": {
        "timezones": {
            "queries": {
                "by_point": "select tzid\nfrom\n    timezones\nwhere\n    within(GeomFromText('POINT(' || :longitude || ' ' || :latitude || ')'), timezones.Geometry)\n    and rowid in (\n        SELECT pkid FROM idx_timezones_Geometry\n        where xmin < :longitude\n        and xmax > :longitude\n        and ymin < :latitude\n        and ymax > :latitude\n    )"
            }
        }
    }
}

The canned query is defined as the by_point key in the queries nested object. I’m also adding license and source information here for the project, because it’s good manners.

We can try this in Datasette on our local machine like so:

datasette timezones.db -m metadata.json \
    --load-extension=/usr/local/lib/mod_spatialite.dylib

Now visiting localhost:8001/timezones/by_point will provide the interface for the query - and adding .json to the URL will turn it into an API.

Vizualizing time zones with Leaflet and GeoJSON

If you browse around in Datasette you’ll quickly run into a rather unfortunate problem. The localhost:8001/timezones/timezones page, which shows the first 100 rows in the table, takes a shockingly long time to load. When it eventually does load you’ll see why: each record includes an enormous binary string containing its the geometry. On my machine just that one page weighs in at 62MB of HTML!

This is bad: rendering that much HTML in one go can block the event loop and cause the application to become unresponsive. That giant blob of binary data isn’t exactly useful for humans, either.

We can make some major improvements here using another Datasette 0.14 feature: custom templates. Let’s start with a replacement template that shows just the length of the binary string instead of attempting to render it.

We’ll do that by over-riding the _rows_and_columns.html include template, which is used by Datasette on both the table page and the page used to display individual rows. Since we only want to over-ride this template for one particular table we’ll create a file called _rows_and_columns-timezones-timezones.html - the file name means this will only over-ride the template timezones table in our timezones database. Here’s our new template:

<table>
    <thead>
        <tr>
            {% for column in display_columns %}
                <th scope="col">{{ column }}</th>
            {% endfor %}
        </tr>
    </thead>
    <tbody>
    {% for row in display_rows %}
        <tr>
            {% for cell in row %}
                <td>
                    {% if cell.column == 'Geometry' %}
                        {{ cell.value|length }} bytes
                    {% else %}
                        {{ cell.value }}
                    {% endif %}
                </td>
            {% endfor %}
        </tr>
    {% endfor %}
    </tbody>
</table>

If we put that in a directory called templates/ we can tell Datasette to use it like this:

datasette timezones.db -m metadata.json \
    --load-extension=/usr/local/lib/mod_spatialite.dylib \
    --template-dir=templates/

Our localhost:8001/timezones/timezones page now looks like this:

But wouldn’t it be neat if we could see the actual shapes of these time zones? It turns out that’s actually pretty easy, using the combination of GeoJSON and the Leaflet mapping library.

GeoJSON is a neat, widely supported standard for encoding geographic information such as polygon shapes as JSON. SpatiaLite ships with built-in GeoJSON support in the form of the AsGeoJSON SQL function. We can use that function to turn any of our time zone geometries into a GeoJSON string:

select AsGeoJSON(Geometry) from timezones where tzid = 'Asia/Tokyo';

If you run that with Datasette you’ll get back a string of GeoJSON. You can paste that into geojson.io to instantly visualize it.

The Leaflet mapping library supports GeoJSON out of the box. We can construct a custom row.html template for our Datasette that loads Leaflet from unpkg.com, uses fetch() to execute the AsGeoJSON query and renders the result in a map on the page. Here’s the result:

And here’s the row-timezones-timezones.html template:

{% extends "row.html" %}
{% block extra_head %}
<link rel="stylesheet" href="https://unpkg.com/leaflet@1.2.0/dist/leaflet.css" integrity="sha512-M2wvCLH6DSRazYeZRIm1JnYyh22purTM+FDB5CsyxtQJYeKq83arPe5wgbNmcFXGqiSH2XR8dT/fJISVA1r/zQ==" crossorigin=""/>
<script src="https://unpkg.com/leaflet@1.2.0/dist/leaflet.js" integrity="sha512-lInM/apFSqyy1o6s89K4iQUKg6ppXEgsVxT35HbzUupEVRh2Eu9Wdl4tHj7dZO0s1uvplcYGmt3498TtHq+log==" crossorigin=""></script>
<style>
#map {
  margin-top: 1em;
  width: 100%;
  height: 400px;
}
</style>
{% endblock %}

{% block content %}
{{ super() }}
<div id="map"></div>
<script>
var pk = location.pathname.split('/').slice(-1)[0];
var tiles = L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
    maxZoom: 19,
    detectRetina: true,
    attribution: '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors, Points &copy 2012 LINZ'
});
var sql = 'select AsGeoJSON(Geometry) from timezones where PK_UID = ' + pk;
fetch('/timezones.json?sql=' + encodeURIComponent(sql)).then(r => r.json()).then(d => {
  var map = L.map('map', {layers: [tiles]});
  var geoJSON = JSON.parse(d.rows[0][0]);
  var layer = L.geoJSON(geoJSON)
  layer.addTo(map);
  map.fitBounds(layer.getBounds());
});
</script>
{% endblock %}

Publishing it to the internet

Normally we would use the datasette publish command to publish our database to Heroku or Zeit Now, but the SpatiaLite dependency means that won’t work for this case. Instead, we need to construct a custom Dockerfile that builds the SpatiaLite module.

Since we’re using Docker, we may as well have the Dockerfile download the shapefiles and build the SpatiaLite database for us all in one go. Here’s a Dockerfile that does exactly that:

FROM python:3.6-slim-stretch

RUN apt update
RUN apt install -y python3-dev gcc spatialite-bin libsqlite3-mod-spatialite wget unzip

RUN pip install https://github.com/simonw/datasette/archive/master.zip

# Download and import the shapefiles
RUN wget https://github.com/evansiroky/timezone-boundary-builder/releases/download/2017c/timezones.shapefile.zip \
    && unzip timezones.shapefile.zip && \
    cd dist && \
    echo ".loadshp combined_shapefile timezones CP1252 23032\nSELECT CreateSpatialIndex('timezones', 'geometry');" | spatialite timezones.db && \
    mv timezones.db .. && \
    cd .. && rm -rf dist && rm timezones.shapefile.zip

ENV SQLITE_EXTENSIONS /usr/lib/x86_64-linux-gnu/mod_spatialite.so

ADD metadata.json metadata.json

ADD templates templates

RUN datasette inspect timezones.db --inspect-file inspect-data.json

EXPOSE 8001

CMD ["datasette", "serve", "timezones.db", "--host", "0.0.0.0", "--cors", "--port", "8001", "--inspect-file", "inspect-data.json", "-m", "metadata.json", "--template-dir", "templates"]

The full code, including the supporting templates, can be found in simonw/timezones-api on GitHub.

If you have Docker installed (Docker for Mac is a one-click install package these days, it’s impressively painless) you can build the container like this:

docker build . -t timezones-api
# Wait for the image to build
docker run -p 8001:8001 timezones-api

Then you can visit http://localhost:8001/ to see your freshly built Datasette in your browser.

The easiest way to publish it online is using Zeit Now. Simply run the now command in the directory containing the Dockerfile and Zeit will upload the entire directory, build the container in the cloud and deploy it with a fresh URL. It’s by far the easiest Docker deployment environment I’ve ever used.

Now can even deploy directly from a public GitHub repository… so you can deploy your own copy of the API by running the following command:

$ now simonw/timezones-api
> Didn't find directory. Searching on GitHub...
> Deploying GitHub repository "simonw/timezones-api" under simonw
> Ready! https://simonw-timezones-api-fbihjcbnog.now.sh (copied to clipboard) [2s]
> Initializing…

Canned queries + SpatiaLite = instant geospatial APIs

Hopefully this has helped illustrate the ease with which Datasette, SpatiaLite and canned queries can be used to create and publish geospatial APIs. Thanks to projects like OpenStreetMap the world is full of high quality open geospatial data. Go build something cool with it!

Tags: geospatial, openstreetmap, sqlite, timezones, docker, datasette, leaflet

django-timezones

2008-08-21T23:18:08+00:00

django-timezones

Models, form fields and a template filter for dealing with timezones in Django.

Tags: django, python, timezones

What's New in Edge Rails: Easier Timezones

2008-04-02T15:39:32+00:00

What's New in Edge Rails: Easier Timezones

Time zones can be a nightmare to get right—if this works well it’s going to make a lot of people’s lives a whole bunch easier.

Via A taste of what's coming in Rails 2.1

Tags: rails, timezones

calendar.timegm()

2007-09-03T01:54:35+00:00

calendar.timegm()

An “unrelated but handy function” that converts a time.gmtime() in to a corresponding Unix timestamp. I’ve been hand-rolling this one for years; never thought to look in calendar.

Via Sam Ruby

Tags: calendar, datetime, python, sam-ruby, time, timezones, unixtimestamp

Django vs feedparser on dates

2007-09-02T10:17:52+00:00

Django vs feedparser on dates

Some useful tips in the comments. I find Python’s timezone stuff endlessly frustrating: I know it can do what I want, but it always takes me a ridiculously long time to figure out the necessary incantations.

Tags: datetime, django, feedparser, python, timezones

A Zoned Defense

2007-03-20T09:51:57+00:00

A Zoned Defense

Using JavaScript’s date.getTimezoneOffset() to detect the user’s timezone and stash it in a cookie.

Tags: javascript, timezones