A collection of tools designed to handle dates and times. These Functions provide the flexibility to change, fine-tune, and display date and time information in a variety of formats, including ISO-8601, Julian days, and Unix timestamps, with options for customization.
For more details, see SQLite Date And Time Functions.
Primary date and time Functions Copy Link
date(X) – Extracts the Date (YYYY-MM-DD) Copy Link
Returns the date component from X.
Example: Get today’s date
date('now');
Output: 2025-02-11
Example: Get the date of a Unix timestamp
date(1700000000, 'unixepoch');
Output: 2023-11-14
time(X) – Extracts the Time (HH:MM:SS) Copy Link
Returns the time component from X.
Example: Get the current time
time('now');
Output: 14:30:00
Example: Get the time for a specific timestamp
time('2025-02-11 08:45:30');
Output: 08:45:30
datetime(X) – Returns Full Date and Time (YYYY-MM-DD HH:MM:SS) Copy Link
Returns the full date and time from X.
Example 1: Get the current date and time
datetime('now');
Output: 2025-02-11 14:30:00
Example 2: Convert Unix timestamp to readable datetime
datetime(1700000000, 'unixepoch');
Output: 2023-11-14 09:46:40
julianday(X) – Returns the Julian Day Number Copy Link
Returns the Julian Day Number, which represents the number of days (including fractional days) since November 24, 4714 BC in the proleptic Julian calendar.
Example 1: Get the Julian day for today
julianday('now');
Output: 2460377.10417
Example 2: Convert a Unix timestamp to Julian day
julianday(1700000000, 'unixepoch');
Output: 2460263.90694
strftime(FORMAT,X) – Formats Date and Time Copy Link
Formats a date/time value into a custom format using placeholders.
Example 1: Get year and month only
strftime('%Y-%m', 'now');
Output: 2025-02
Example 2: Get full date-time with milliseconds
strftime('%Y-%m-%d %H:%M:%f', 'now');
Output: 2025-02-11 14:30:00.000
Example 3: Get day of the week (0=Sunday, 6=Saturday)
strftime('%w', 'now');
Output: 2 (Tuesday)
unixepoch() – Convert Unix Timestamp to Datetime Copy Link
This function converts a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) into a readable datetime.
Example 1: Convert Unix timestamp to date
date(1700000000, 'unixepoch');
Output: 2023-11-14
Example 2: Convert Unix timestamp to full datetime
datetime(1700000000, 'unixepoch');
Output: 2023-11-14 09:46:40
timediff(time-value,time-value) – Calculate the difference between two time-values Copy Link
This Function returns a string that describes the amount of time that must be added to B in order to reach time A and returns the result as a human-readable string in the format (+|-)YYYY-MM-DD HH:MM:SS.SSS.
Usage:
timediff(time-value1, time-value2)
Parameters:
time-value1: The first time value.time-value2: The second time value.
Example:
timediff('2025-02-15', '2025-03-15');
Output: -0000-01-00 00:00:00.000
This output indicates a difference of 1 month between the two dates.
Alternative Methods: You can calculate the difference between two dates or times using functions like julianday() or by subtracting Unix timestamps obtained via strftime('%s', ...). These methods return the difference in days or seconds, respectively.
Example Using julianday():
julianday('2025-03-15') - julianday('2025-02-15') AS difference_in_days;
Output: 28.0
This indicates a 28-day difference between the two dates.
Example Using Unix Timestamps:
strftime('%s', '2025-03-15') - strftime('%s', '2025-02-15') AS difference_in_seconds;
Output: 2419200
This indicates a difference of 2,419,200 seconds (which equals 28 days).
Customize Functions with Modifiers Copy Link
Modifiers can be applied to these Functions to adjust the date and time values. Modifiers are applied in order, from left to right, and their sequence affects the final output. Some common modifiers include:
| Modifier | Description | Example |
|---|---|---|
+NNN days | Adds NNN days | date('2025-06-10', '+5 days') → 2025-06-15 |
-NNN days | Subtracts NNN days | date('2025-06-10', '-5 days') → 2025-06-05 |
+NNN hours | Adds NNN hours | time('14:00:00', '+2 hours') → 16:00:00 |
-NNN hours | Subtracts NNN hours | time('14:00:00', '-2 hours') → 12:00:00 |
+NNN minutes | Adds NNN minutes | time('14:00:00', '+30 minutes') → 14:30:00 |
-NNN minutes | Subtracts NNN minutes | time('14:00:00', '-15 minutes') → 13:45:00 |
start of month | Moves to the first day of the month | date('2025-06-15', 'start of month') → 2025-06-01 |
start of year | Moves to January 1st of the year | date('2025-06-15', 'start of year') → 2025-01-01 |
weekday N | Moves to the next Nth weekday (0=Sunday, 1=Monday, …) | date('2025-05-01', 'weekday 5') → 2025-05-02 |
unixepoch | Converts Unix timestamp to a date | datetime(1700000000, 'unixepoch') → 2023-11-14 09:46:40 |
localtime | Converts UTC to local time | datetime('now', 'localtime') |
utc | Converts local time to UTC | datetime('now', 'utc') |
Combining Multiple Modifiers Copy Link
You can chain multiple modifiers together.
Example: Find the last day of the current month
date('now', 'start of month', '+1 month', '-1 day');
Output: If today is 2025-02-11, the result will be 2025-02-28 (last day of February).
Example: Get the timestamp for “3 days ago, at the start of the month”
datetime('now', 'start of month', '-3 days');
Output: If today is 2025-02-11, the result will be 2025-01-29.
Don’t stop here! Copy Link
Explore our User Guide.
For a complete tutorial on how to create a Flow, see “How to create a Flow in Wiresk”