Time Functions (time::*)

Level 6 — Advanced Querying & Functions The standard library module in SurrealDB for date, time, and timestamp manipulation (time::now(), time::floor(), time::format(), time::group()), enabling date truncations and duration arithmetic natively.

1. Prerequisites

• Built-in Functions Overview — The parent library context.
• datetime / duration — Temporal types.

2. Term Category

Query Feature (datetime parsing & arithmetic builtin functions): - Database Command / Tool

3. Explanation

(1) Design Motivation — "Why did we design this?"

Date and time operations are essential for building analytics and tracking software:

• Truncating timestamps to group metrics by day, week, or month (time::floor() / time::group()).
• Extracting specific components (day of week, year, hour) from timestamps.
• Formatting raw UTC datetimes into human-readable strings for API responses.

In SQL (PostgreSQL), developers use DATE_TRUNC(), EXTRACT(), and TO_CHAR(). In MongoDB, developers use aggregation date operators ($dateTrunc, $dateToString).

We designed the time::* module in SurrealDB to provide a unified date/time suite. Functions like time::now() fetch current UTC timestamps, time::floor() truncates dates cleanly, and time::format() transforms ISO timestamps into any custom string template, avoiding date parsing bugs.

(2) Key Function Categories

1. Current Time & Timestamp Creation

• time::now(): Returns the current UTC timestamp (e.g. d"2026-07-22T09:23:00Z").
• time::from::unix(timestamp): Converts Unix epoch seconds to a datetime.
• time::from::millis(ms): Converts epoch milliseconds to a datetime.

2. Truncation & Grouping

• time::floor(dt, duration): Truncates (rounds down) a timestamp to the nearest duration boundary (e.g., time::floor(d"2026-07-22T09:45:00Z", 1h) returns d"2026-07-22T09:00:00Z").
• time::group(dt, "day"|"month"|"year"): Groups a timestamp by calendar periods for GROUP BY aggregations.

3. Component Extraction & Formatting

• time::year(dt) / time::month(dt) / time::day(dt) / time::wday(dt): Extracts specific date units.
• time::format(dt, format_string): Formats datetime to string using format specifications (e.g. %Y-%m-%d).

(3) Reality Metaphor (The Digital Grandfather Clock)

Imagine a master clockmaker's bench:

• time::now: Looking at a digital atomic clock displaying exact nanoseconds UTC.
• time::floor(dt, 1d): Flipping the calendar page back to midnight (00:00:00) of the current day.
• time::format: Taking a raw timestamp stamp and printing it neatly onto a paper postcard in "YYYY-MM-DD" format.

(4) Code Examples

Using time::* Functions in SurrealQL

-- 1. Setting timestamps on record creation/update
CREATE order SET 
  placed_at = time::now(),
  delivery_estimate = time::now() + 3d;

-- 2. Truncating timestamps to group metrics by hour
SELECT 
  time::floor(created_at, 1h) AS hour_bucket,
  count() AS total_requests
FROM api_log
GROUP BY hour_bucket;

-- 3. Formatting dates for API output
SELECT 
  id,
  time::format(created_at, "%B %d, %Y") AS formatted_date
FROM post;
-- Returns: "July 22, 2026"

4. Common Mistakes & Pitfalls

Mistake 1: Using SQL function names like 'NOW()' or 'DATE_TRUNC()' instead of 'time::now()' and 'time::floor()'

The mistake: Writing SELECT * FROM logs WHERE created_at > NOW() - INTERVAL '7 days';.

Why it's wrong: SurrealQL does not support PostgreSQL function names or INTERVAL text strings. Executing this will result in parser syntax errors.

Fix: Use SurrealDB's time::now() function and native duration literals (7d):

-- BAD
SELECT * FROM logs WHERE created_at > NOW() - INTERVAL '7 days';

-- GOOD
SELECT * FROM logs WHERE created_at > time::now() - 7d;

Mistake 2: Passing Plain Strings to time:: Functions Without d"..." Prefix

The mistake: Passing '2026-01-01' into time::year('2026-01-01').

Why it's wrong: time:: functions expect native datetime primitives. Pass d'2026-01-01T00:00:00Z' or <datetime> '2026-01-01'. Plain strings trigger type errors.

Incorrect:

RETURN time::year("2026-01-01"); // ❌ Expected datetime, got string!

Fix:

RETURN time::year(d"2026-01-01T00:00:00Z"); // Native datetime input

Mistake 3: Confusing time::now() (Current Timestamp) with Static Constant Strings

The mistake: Hardcoding "2026-01-01" in DEFAULT field initializers expecting real-time updates.

Why it's wrong: Use time::now() to capture the dynamic current ISO timestamp upon record creation.

Incorrect:

DEFINE FIELD created_at ON TABLE user TYPE datetime DEFAULT d"2026-01-01T00:00:00Z"; // Static fixed date!

Fix:

DEFINE FIELD created_at ON TABLE user TYPE datetime DEFAULT time::now(); // Dynamic current time

5. Practice Exercises

Exercise 1: Current Time and Temporal Arithmetic

Scenario: Query the current UTC timestamp using time::now(), calculate tomorrow (time::now() + 1d), and format as an ISO date string.

Requirements:

1. Select time::now() and time::now() + 1d.

SELECT 
    time::now() AS current_utc,
    time::now() + 1d AS tomorrow_utc;

Exercise 2: Time Grouping with time::floor()

Scenario: An analytics query groups audit logs into 1-hour time buckets using time::floor().

Requirements:

1. Group logs by time::floor(timestamp, 1h).
2. Count logs per hour bucket.

SELECT 
    time::floor(timestamp, 1h) AS hour_bucket,
    count() AS total_events
FROM audit_log
GROUP BY hour_bucket;

Exercise 3: Extracting Date Components

Scenario: Extract the year, month, and day components from a stored datetime field using time::year(), time::month(), and time::day().

Requirements:

1. Project time::year(created_at), time::month(created_at), time::day(created_at).

SELECT 
    time::year(created_at) AS yr,
    time::month(created_at) AS mo,
    time::day(created_at) AS dy
FROM user:alice;

6. Related Terms

• Built-in Functions Overview — The parent library.
• datetime / duration — Temporal types.
• SHOW CHANGES FOR TABLE ... SINCE ... — Related concept: SHOW CHANGES FOR TABLE ... SINCE ....

7. Key Takeaways

• The time::* module handles timestamps, truncations, and formatting.
• time::now() returns the current UTC timestamp.
• time::floor(dt, duration) rounds timestamps down to duration buckets (e.g. 1h, 1d).
• Duration arithmetic (time::now() + 7d) works directly with datetime objects.
• time::format(dt, "%Y-%m-%d") formats timestamps into custom strings.