logoalt Hacker News

pseudalopex • yesterday at 9:54 PM • 2 replies • view on HN

This could enforce dates are strings. They wanted to enforce dates are dates I thought.

Replies

simonw • yesterday at 10:29 PM

  create table events (
    id integer primary key,
    name text not null,
    event_date text not null check (
      -- YYYY-MM-DD
      event_date glob '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
      and date(event_date) is not null
      and date(event_date) = event_date
    )
  );
In Python that raises this error if the date is invalid:

  sqlite3.IntegrityError: CHECK constraint failed:
    event_date glob '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
➕ show 2 replies
zimmi • yesterday at 10:02 PM

Storing dates as INTEGER (year * 10000 + month * 100 + day, e.g. 20260530) is not so bad. Proper date / timestamp types would be great though.

➕ show 1 reply