How to get Epoch/UNIX Timestamp in SQLite

In SQLite, you can get Epoch timestamps using various date and time functions. This article explores different ways to retrieve Epoch timestamps in SQLite and provides practical use cases.

Using the strftime() Function

In SQLite, you can use the strftime() function to fetch the current Epoch timestamp. This function takes a format string as an argument and returns the current timestamp in the specified format.

Example SQL Query

To obtain the current Epoch timestamp using the strftime() function, execute the following SQL query in your SQLite database:

SELECT strftime('%s', 'now') AS current_epoch_timestamp;

The above query will return the current Epoch timestamp as the result, labeled as "current_epoch_timestamp."

Use Case 1: Logging Timestamps

To log data with timestamps, you can use the strftime() function to obtain the current timestamp and insert it into a database table. Here's an example SQL query:

INSERT INTO sensor_data (data, timestamp)
VALUES ('Sensor data reading...', strftime('%s', 'now'));

In this SQL query, we're inserting sensor data along with the current Epoch timestamp into a table called "sensor_data."

Use Case 2: Time Duration Calculation

If you need to measure the time taken for a task, you can use the strftime() function to record start and end times. Here's how you can calculate the time elapsed in seconds:

INSERT INTO task_log (start_time, task_description) VALUES (strftime('%s', 'now'), 'Task started');

-- Perform a task or operation

UPDATE task_log SET end_time = strftime('%s', 'now') WHERE task_description = 'Task started';

SELECT end_time - start_time AS time_elapsed_seconds FROM task_log WHERE task_description = 'Task started';

This SQL script records the start time when a task begins, and the end time when it ends using the strftime() function. It then calculates the time elapsed in seconds by subtracting the start time from the end time.

Use Case 3: Expiry Timestamp for Cache

If you need to set an expiry timestamp for cache data, you can use the strftime() function to calculate the expiry time based on the current timestamp. Here's an example SQL query:

INSERT INTO cached_data (data, expiry_timestamp)
VALUES ('Cached data...', strftime('%s', 'now', '+600 seconds'));

In this SQL query, the current Epoch timestamp is obtained using strftime(), and then 600 seconds (10 minutes) are added to it to determine the cache expiry time. The data is inserted into a table called "cached_data."

Use Case 4: Event Scheduling

To schedule an event to occur in the future, you can use the strftime() function to calculate the event time. Here's an example SQL script:

INSERT INTO event_schedule (event_description, event_timestamp)
VALUES ('Event scheduled for 1 hour from now', strftime('%s', 'now', '+1 hour'));

-- Wait for the event to occur

SELECT * FROM event_schedule WHERE event_timestamp <= strftime('%s', 'now');

This SQL script calculates the event time as one hour from the current time and inserts it into a table called "event_schedule." It then waits for the event to occur by checking for events with timestamps less than or equal to the current timestamp.

These code examples illustrate how to get Epoch/UNIX timestamps in SQLite and showcase their applications in practical scenarios. Whether you need to log data with timestamps, calculate time durations, set cache expiry times, or schedule events, Epoch timestamps are useful tools in SQLite for time-related operations.