Temporal data¶

Synalog has one rule for TIMESTAMP, DATE, DATETIME and TIME columns: never apply arithmetic or comparison directly to a temporal column. Engines disagree wildly on temporal semantics; the portable pipeline is string-based.

The pipeline¶

ToString(x) — convert the temporal value to its ISO string form.
Substr(s, i, l) — extract the part you need (1-based indexing).
ToInt64(x) — only if you need arithmetic on the part.

# Year-month for grouping
month == Substr(ToString(created_at), 1, 7);    # "2024-01"

# Date only
date == Substr(ToString(created_at), 1, 10);    # "2024-01-15"

# Hour as int
hour == ToInt64(Substr(ToString(timestamp), 12, 2));

# Year/month/day as ints
year  == ToInt64(Substr(date_str, 1, 4));
month == ToInt64(Substr(date_str, 6, 2));
day   == ToInt64(Substr(date_str, 9, 2));

Date ranges¶

ISO-format strings compare correctly as strings, so range filters are simple:

RecentOrders(order_id:) :-
  Orders(order_id:, created_at:),
  ToString(created_at) >= "2024-01-01",
  ToString(created_at) < "2024-02-01";

Grouping by month¶

@OrderBy(MonthlyOrders, "month");
MonthlyOrders(month:, count? += 1) distinct :-
  Orders(created_at:),
  month == Substr(ToString(created_at), 1, 7);

`Today` and `Now`¶

Two built-in concepts read the engine's clock:

Today(date:) — today's date as a "YYYY-MM-DD" string.
Now(timestamp:) — the current instant as the dialect's native timestamp.

Now is deliberately the most precise value the engine offers; every coarser part — date, time of day, hour, minute — is derived from it through the pipeline (ToString → Substr), so there is no separate time: or date: field to keep in sync.

Use them for any "today"- or "now"-relative logic. Do not create, update or delete them — the compiler inlines a one-row relation per dialect (using each engine's native current-date/timestamp SQL), so they need no runtime table and work on every engine, including BigQuery and read-only remote catalogs.

ThisMonthOrders(order_id:, created_at:) :-
  Orders(order_id:, created_at:),
  Today(date:),
  Substr(ToString(created_at), 1, 7) == Substr(date, 1, 7);

Deriving parts of `Now`¶

The time of day, hour, and date all come out of the same Substr pipeline you use for any temporal column:

NowParts(date:, time:, hour:) :-
  Now(timestamp:),
  date == Substr(ToString(timestamp), 1, 10),    # "2026-06-13"
  time == Substr(ToString(timestamp), 12, 8),    # "14:53:09"
  hour == ToInt64(Substr(ToString(timestamp), 12, 2));

Within one statement the engine reads Today and Now from the same clock, so the date prefix of Now's timestamp equals Today's date.

Relative dates and times¶

"Yesterday" and "ten minutes ago" need date/timestamp arithmetic. Do it the same portable way as everything else: pull the parts out with Substr, turn them into integers with ToInt64, do the math, and reassemble with ToString. Never reach for SqlExpr to subtract an interval — raw SQL is unsafe and non-portable, and the verifier rejects it. Integer division is also not portable, so use % (exact) and conditionals instead.

Two small helpers — month length (with the leap-year rule) and two-digit zero-padding:

DaysInMonth(y, m) = n :-
  leap == (if (y % 4 == 0) && ((y % 100 != 0) || (y % 400 == 0)) then 1 else 0),
  n == (if m == 2 then 28 + leap
        else if (m == 4) || (m == 6) || (m == 9) || (m == 11) then 30
        else 31);

Pad2(x) = (if x < 10 then "0" ++ ToString(x) else ToString(x));

Yesterday subtracts one day, borrowing into the previous month/year at a boundary:

Yesterday(date) = ToString(py) ++ "-" ++ Pad2(pm) ++ "-" ++ Pad2(pd) :-
  y == ToInt64(Substr(date, 1, 4)),
  m == ToInt64(Substr(date, 6, 2)),
  d == ToInt64(Substr(date, 9, 2)),
  py == (if d > 1 then y else if m == 1 then y - 1 else y),
  pm == (if d > 1 then m else if m == 1 then 12 else m - 1),
  pd == (if d > 1 then d - 1 else DaysInMonth(py, pm));

TenMinutesAgo subtracts ten minutes, borrowing into the hour and (at midnight) reusing Yesterday for the date. Ten minutes crosses at most one hour, and one hour-borrow crosses at most one day, so no division is needed:

TenMinutesAgo(ts) = ndate ++ " " ++ Pad2(nhh) ++ ":" ++ Pad2(nmm) ++ ":" ++ ss :-
  date == Substr(ts, 1, 10),
  hh == ToInt64(Substr(ts, 12, 2)),
  mm == ToInt64(Substr(ts, 15, 2)),
  ss == Substr(ts, 18, 2),
  nmm  == (if mm >= 10 then mm - 10 else mm + 50),
  hh1  == (if mm >= 10 then hh else hh - 1),     # borrow an hour when mm < 10
  nhh  == (if hh1 < 0 then 23 else hh1),
  ndate == (if hh1 < 0 then Yesterday(date) else date);

Apply them to Today/Now and filter with plain string comparison:

RecentlyCreated(order_id:) :-
  Orders(order_id:, created_at:),
  Now(timestamp:),
  cutoff == TenMinutesAgo(ToString(timestamp)),
  ToString(created_at) >= cutoff;

Temporal edges¶

For relationships with validity periods, extract start_date/end_date through the pipeline when defining the edge, then filter with Today:

@OrderBy(MemberOf, "employee_id");
MemberOf(employee_id:, team_id:, start_date:, end_date:) distinct :-
  Employee(employee_id:), Team(team_id:),
  TeamAssignments(employee_id:, team_id:, started_at:, ended_at:),
  start_date == Substr(ToString(started_at), 1, 10),
  end_date   == Substr(ToString(ended_at), 1, 10);

@OrderBy(CurrentMember, "employee");
CurrentMember(employee:, team:) :-
  MemberOf(employee_id:, team_id:, start_date:, end_date:),
  Employee(employee_id:, name: employee),
  Team(team_id:, name: team),
  Today(date:),
  start_date <= date, end_date >= date;

Two periods [s1, e1] and [s2, e2] overlap when s1 <= e2 && s2 <= e1.

Complete example¶

Month grouping, a date-range filter, integer hour extraction over timestamped orders, and a Today-based "not yet expired" filter over subscriptions:

# run: MonthlyOrders, JanuaryOrders, OrderHours, ActiveSubscriptions
@Engine("duckdb");

# Tables
Orders(order_id: 1, created_at: "2024-01-15 10:30:00");
Orders(order_id: 2, created_at: "2024-01-28 18:05:00");
Orders(order_id: 3, created_at: "2024-02-03 09:00:00");
Orders(order_id: 4, created_at: "2024-02-14 22:45:00");
Orders(order_id: 5, created_at: "2024-03-01 08:15:00");

Subscriptions(sub_id: 1, expires: "2024-06-30");
Subscriptions(sub_id: 2, expires: "2030-06-30");

# Rules

## The pipeline: ToString -> Substr -> (ToInt64 if needed).
@OrderBy(MonthlyOrders, "month");
MonthlyOrders(month:, count? += 1) distinct :-
  Orders(created_at:),
  month == Substr(ToString(created_at), 1, 7);

## ISO strings compare correctly: date-range filters are plain comparisons.
@OrderBy(JanuaryOrders, "order_id");
JanuaryOrders(order_id:) :-
  Orders(order_id:, created_at:),
  ToString(created_at) >= "2024-01-01",
  ToString(created_at) < "2024-02-01";

## Extract parts as integers for arithmetic.
@OrderBy(OrderHours, "order_id");
OrderHours(order_id:, hour:, is_evening:) :-
  Orders(order_id:, created_at:),
  hour == ToInt64(Substr(ToString(created_at), 12, 2)),
  is_evening == (if hour >= 18 then "yes" else "no");

## "Today"-relative logic with the Today built-in concept.
@OrderBy(ActiveSubscriptions, "sub_id");
ActiveSubscriptions(sub_id:, expires:) :-
  Subscriptions(sub_id:, expires:),
  Today(date:),
  expires >= date;

Generated SQL and execution results

$ synalog.check('temporal.l')
No errors found.

$ synalog.compile('temporal.l', 'MonthlyOrders')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_Orders AS (SELECT * FROM (

    SELECT
      1 AS order_id,
      E'2024-01-15 10:30:00' AS created_at
   UNION ALL

    SELECT
      2 AS order_id,
      E'2024-01-28 18:05:00' AS created_at
   UNION ALL

    SELECT
      3 AS order_id,
      E'2024-02-03 09:00:00' AS created_at
   UNION ALL

    SELECT
      4 AS order_id,
      E'2024-02-14 22:45:00' AS created_at
   UNION ALL

    SELECT
      5 AS order_id,
      E'2024-03-01 08:15:00' AS created_at

) AS UNUSED_TABLE_NAME  )
SELECT
  SUBSTR(CAST(Orders.created_at AS TEXT), 1, 7) AS month,
  SUM(1) AS count
FROM
  t_0_Orders AS Orders
GROUP BY SUBSTR(CAST(Orders.created_at AS TEXT), 1, 7) ORDER BY month;

-- Executed on DuckDB:
| month   | count |
|---------|-------|
| 2024-01 | 2     |
| 2024-02 | 2     |
| 2024-03 | 1     |
(3 rows)

$ synalog.compile('temporal.l', 'JanuaryOrders')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_Orders AS (SELECT * FROM (

    SELECT
      1 AS order_id,
      E'2024-01-15 10:30:00' AS created_at
   UNION ALL

    SELECT
      2 AS order_id,
      E'2024-01-28 18:05:00' AS created_at
   UNION ALL

    SELECT
      3 AS order_id,
      E'2024-02-03 09:00:00' AS created_at
   UNION ALL

    SELECT
      4 AS order_id,
      E'2024-02-14 22:45:00' AS created_at
   UNION ALL

    SELECT
      5 AS order_id,
      E'2024-03-01 08:15:00' AS created_at

) AS UNUSED_TABLE_NAME  )
SELECT
  Orders.order_id AS order_id
FROM
  t_0_Orders AS Orders
WHERE
  (CAST(Orders.created_at AS TEXT) >= E'2024-01-01') AND
  (CAST(Orders.created_at AS TEXT) < E'2024-02-01') ORDER BY order_id;

-- Executed on DuckDB:
| order_id |
|----------|
| 1        |
| 2        |
(2 rows)

$ synalog.compile('temporal.l', 'OrderHours')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_Orders AS (SELECT * FROM (

    SELECT
      1 AS order_id,
      E'2024-01-15 10:30:00' AS created_at
   UNION ALL

    SELECT
      2 AS order_id,
      E'2024-01-28 18:05:00' AS created_at
   UNION ALL

    SELECT
      3 AS order_id,
      E'2024-02-03 09:00:00' AS created_at
   UNION ALL

    SELECT
      4 AS order_id,
      E'2024-02-14 22:45:00' AS created_at
   UNION ALL

    SELECT
      5 AS order_id,
      E'2024-03-01 08:15:00' AS created_at

) AS UNUSED_TABLE_NAME  )
SELECT
  Orders.order_id AS order_id,
  CAST(SUBSTR(CAST(Orders.created_at AS TEXT), 12, 2) AS INT64) AS hour,
  CASE WHEN (CAST(SUBSTR(CAST(Orders.created_at AS TEXT), 12, 2) AS INT64) >= 18) THEN E'yes' ELSE E'no' END AS is_evening
FROM
  t_0_Orders AS Orders ORDER BY order_id;

-- Executed on DuckDB:
| order_id | hour | is_evening |
|----------|------|------------|
| 1        | 10   | no         |
| 2        | 18   | yes        |
| 3        | 9    | no         |
| 4        | 22   | yes        |
| 5        | 8    | no         |
(5 rows)

$ synalog.compile('temporal.l', 'ActiveSubscriptions')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_Subscriptions AS (SELECT * FROM (

    SELECT
      1 AS sub_id,
      E'2024-06-30' AS expires
   UNION ALL

    SELECT
      2 AS sub_id,
      E'2030-06-30' AS expires

) AS UNUSED_TABLE_NAME  )
SELECT
  Subscriptions.sub_id AS sub_id,
  Subscriptions.expires AS expires
FROM
  t_0_Subscriptions AS Subscriptions, (SELECT strftime(current_date, '%Y-%m-%d') AS date) AS Today
WHERE
  (Subscriptions.expires >= Today.date) ORDER BY sub_id;

-- Executed on DuckDB:
| sub_id | expires    |
|--------|------------|
| 2      | 2030-06-30 |
(1 row)