SQLite current_timestamp Function

Summary: in this tutorial, you will learn how to use the SQLite current_timestamp function to obtain the current date and time in UTC.

Introduction to the SQLite current_timestamp function

The current_timestamp function returns the current date and time in UTC in the format YYYY-MM-DD HH:MM:SS.

Here’s the syntax of the current_timestamp function:

<code>current_timestamp</code>Code language: SQL (Structured Query Language) (sql)

Note that the current_timestamp function does not have the opening and closing parentheses ()

In practice, you’ll use the current_timestamp function to insert or update a column value with the current date and time.

SQLite current_timestamp function examples

Let’s explore some examples of using the current_timestamp function.

1) Basic current_timestamp function example

The following example uses the current_timestamp function to return the current date and time in UTC:

SELECT current_timestamp;Code language: SQL (Structured Query Language) (sql)

Output:

current_timestamp
-------------------
2024-04-12 11:37:46Code language: SQL (Structured Query Language) (sql)

To get the current date and time in local time, you pass the result of the current_timestamp function to the datetime() function and use the 'localtime' modifier as follows:

SELECT 
   datetime(current_timestamp, 'localtime') current_timestamp;Code language: SQL (Structured Query Language) (sql)

Output:

current_timestamp
-------------------
2024-04-12 18:41:12Code language: SQL (Structured Query Language) (sql)

2) Using the current_timestamp function as the default value for a column

First, create a table called notes:

CREATE TABLE notes(
    id INTEGER PRIMARY KEY,
    note TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT current_timestamp,
    updated_at TEXT NOT NULL DEFAULT current_timestamp
);Code language: SQL (Structured Query Language) (sql)

In the notes table, both created_at and updated_at columns have default values of the current date and time. This means that when you insert rows into the notes table, these columns will automatically be populated with the current timestamp.

Second, insert a row into the notes table:

INSERT INTO notes(note)
VALUES('Learn SQLite current_timestamp function')
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

id | note                                    | created_at          | updated_at
---+-----------------------------------------+---------------------+--------------------
1  | Learn SQLite current_timestamp function | 2024-04-12 12:14:46 | 2024-04-12 12:14:46

(1 row)Code language: SQL (Structured Query Language) (sql)

The output indicates that SQLite uses the current date and time in UTC returned by the current_timestamp function to insert into the created_at and updated_at columns.

Third, create a conditional trigger that is invoked when a row in the notes table is updated, and the updated_at column is not the same as the current date and time:

CREATE TRIGGER update_notes_updated_at
AFTER UPDATE ON notes
WHEN old.updated_at <> current_timestamp
BEGIN
     UPDATE notes
    SET updated_at = CURRENT_TIMESTAMP
    WHERE id = OLD.id;
END;Code language: SQL (Structured Query Language) (sql)

Without the WHEN clause, the trigger will create an infinite loop because it updates the updated_at column, which in turn fires the AFTER UPDATE trigger again.

Fourth, update the row with id 1:

UPDATE notes
SET
  note = 'Learn SQLite current_timestamp'
WHERE
  id = 1;Code language: SQL (Structured Query Language) (sql)

Finally, retrieve data from the notes table:

SELECT * FROM notes;

Output:

id | note                            | created_at          | updated_at
---+---------------------------------+---------------------+--------------------
1  | Learn SQLite current_timestamp  | 2024-04-12 12:14:46 | 2024-04-12 12:17:32

(1 row)Code language: SQL (Structured Query Language) (sql)

The output indicates that the updated_at column is updated to the time when the row was updated.

Summary

• Use the current_timestamp function to get the current date and time in UTC.