Simon Willison's Weblog: datetime

SQLite CREATE TABLE: The DEFAULT clause

2025-05-08T22:37:44+00:00

SQLite CREATE TABLE: The DEFAULT clause

If your SQLite create table statement includes a line like this:

CREATE TABLE alerts (
    -- ...
    alert_created_at text default current_timestamp
)

current_timestamp will be replaced with a UTC timestamp in the format 2025-05-08 22:19:33. You can also use current_time for HH:MM:SS and current_date for YYYY-MM-DD, again using UTC.

Posting this here because I hadn't previously noticed that this defaults to UTC, which is a useful detail. It's also a strong vote in favor of YYYY-MM-DD HH:MM:SS as a string format for use with SQLite, which doesn't otherwise provide a formal datetime type.

Tags: datetime, sql, sqlite

High-precision date/time in SQLite

2024-08-09T15:31:40+00:00

High-precision date/time in SQLite

Another neat SQLite extension from Anton Zhiyanov. sqlean-time (C source code here) implements high-precision time and date functions for SQLite, modeled after the design used by Go.

A time is stored as a 64 bit signed integer seconds 0001-01-01 00:00:00 UTC - signed so you can represent dates in the past using a negative number - plus a 32 bit integer of nanoseconds - combined into a a 13 byte internal representation that can be stored in a BLOB column.

A duration uses a 64-bit number of nanoseconds, representing values up to roughly 290 years.

Anton includes dozens of functions for parsing, displaying, truncating, extracting fields and converting to and from Unix timestamps.

Via lobste.rs

Tags: datetime, go, sqlite, anton-zhiyanov

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

Tips on using python's datetime module

2009-07-06T14:20:12+00:00

Tips on using python's datetime module

Wow. I’ve run in to problems with datetime and timezones before, but I had no idea how intrinsic those problems were to the design of the library.

Tags: dates, datetime, python, times

Making the HTML5 time element safe for historians

2009-04-06T14:01:37+00:00

Making the HTML5 time element safe for historians

PPK presents a detailed history of dates and calendars and points out that the HTML5 time element is ill prepared to faithfully represent the kind of dates historians are interested in.

Tags: calendars, dates, datetime, historians, history, html5, ppk, standards, time

Datejs - A JavaScript Date Library

2007-12-03T21:01:10+00:00

Datejs - A JavaScript Date Library

Building a date API around chaining—Date.today().next().thursday()—is a neat concept. I’d like to see that adapted for Python’s datetime library.

Tags: chaining, datejs, datetime, javascript, python

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