The SQLite now argument is stable within the same query

I stumbled across an interesting little detail of SQLite today, running the following query:

select strftime('%s','now') || substr(strftime('%f','now'),4) as t1
union all
select strftime('%s','now') || substr(strftime('%f','now'),4)
union all
select strftime('%s','now') || substr(strftime('%f','now'),4)
union all
select strftime('%s','now') || substr(strftime('%f','now'),4)
union all
select strftime('%s','now') || substr(strftime('%f','now'),4)
union all
select strftime('%s','now') || substr(strftime('%f','now'),4)

That strftime() pattern is described here and in this TIL, it returns the current Unix timestamp in milliseconds.

The result of the above query is:

I was expecting each timestamp to differ by a few milliseconds, but they're all the same.

I spotted why in the SQLite Date and Time Functions documentation:

The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call.

Related

• sqlite Unix timestamp in milliseconds in SQLite - 2023-04-08
• sqlite SQLite timestamps with floating point seconds - 2024-03-13
• sqlite Querying for items stored in UTC that were created on a Thursday in PST - 2021-03-12
• sqlite Calculating the size of a SQLite database file using SQL - 2023-08-21
• sqlite List all columns in a SQLite database - 2020-05-06
• sqlite Finding the SQLite version used by Web SQL in Chrome - 2022-10-28
• sqlite Ordered group_concat() in SQLite - 2022-02-06
• sqlite Track timestamped changes to a SQLite table using triggers - 2021-08-19
• sqlite SQLite can use more than one index for a query - 2022-12-11
• sqlite SQLite pragma_function_list() - 2023-01-27

Created 2023-02-05T13:23:12-08:00 · Edit