{"id":3016,"date":"2024-04-12T19:30:50","date_gmt":"2024-04-12T12:30:50","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=3016"},"modified":"2024-04-12T21:49:54","modified_gmt":"2024-04-12T14:49:54","slug":"sqlite-current_timestamp","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-date-functions\/sqlite-current_timestamp\/","title":{"rendered":"SQLite current_timestamp Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQLite current_timestamp<\/code> function to obtain the current date and time in UTC.<\/p>\n\n\n\n

Introduction to the SQLite current_timestamp function<\/h2>\n\n\n\n

The current_timestamp<\/code> function returns the current date and time in UTC in the format YYYY-MM-DD HH:MM:SS<\/code>.<\/p>\n\n\n\n

Here’s the syntax of the current_timestamp<\/code> function:<\/p>\n\n\n

<code>current_timestamp<\/code><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the current_timestamp<\/code> function does not have the opening and closing parentheses ()<\/p>\n\n\n\n
In practice, you’ll use the current_timestamp<\/code> function to insert or update a column value with the current date and time.<\/p>\n\n\n\n
SQLite current_timestamp function examples<\/h2>\n\n\n\n
Let’s explore some examples of using the current_timestamp<\/code> function.<\/p>\n\n\n\n
1) Basic current_timestamp function example<\/h3>\n\n\n\n
The following example uses the current_timestamp<\/code> function to return the current date and time in UTC:<\/p>\n\n\n
SELECT<\/span> current_timestamp<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
current_timestamp\n-------------------<\/span>\n2024-04-12 11:37:46<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To get the current date and time in local time, you pass the result of the current_timestamp<\/code> function to the datetime()<\/a>  function and use the 'localtime'<\/code> modifier as follows:<\/p>\n\n\n
SELECT<\/span> \n   datetime(current_timestamp<\/span>, 'localtime'<\/span>) current_timestamp<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
current_timestamp\n-------------------<\/span>\n2024-04-12 18:41:12<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using the current_timestamp function as the default value for a column<\/h3>\n\n\n\n
First, create a table<\/a> called notes<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> notes(\n    id<\/span> INTEGER<\/span> PRIMARY KEY<\/span>,\n    note TEXT<\/span> NOT<\/span> NULL<\/span>,\n    created_at TEXT<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> current_timestamp<\/span>,\n    updated_at TEXT<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> current_timestamp<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the notes<\/code> table, both created_at<\/code> and updated_at<\/code> columns have default values of the current date and time. This means that when you insert rows into the notes<\/code> table, these columns will automatically be populated with the current timestamp.<\/p>\n\n\n\n
INSERT<\/span> INTO<\/span> notes(note)\nVALUES<\/span>('Learn SQLite current_timestamp function'<\/span>)\nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
id | note                                    | created_at          | updated_at\n---+-----------------------------------------+---------------------+--------------------<\/span>\n1  | Learn SQLite current_timestamp function | 2024-04-12 12:14:46 | 2024-04-12 12:14:46\n\n(1 row)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that SQLite uses the current date and time in UTC returned by the current_timestamp<\/code> function to insert into the created_at<\/code> and updated_at<\/code> columns.<\/p>\n\n\n\n
CREATE<\/span> TRIGGER<\/span> update_notes_updated_at\nAFTER<\/span> UPDATE<\/span> ON<\/span> notes\nWHEN<\/span> old.updated_at <> current_timestamp<\/span>\nBEGIN<\/span>\n     UPDATE<\/span> notes\n    SET<\/span> updated_at = CURRENT_TIMESTAMP<\/span>\n    WHERE<\/span> id<\/span> = OLD.id;\nEND<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Without the WHEN<\/code> clause, the trigger will create an infinite loop because it updates the updated_at column, which in turn fires the AFTER UPDATE<\/code> trigger again.<\/p>\n\n\n\n
UPDATE<\/span> notes\nSET<\/span>\n  note = 'Learn SQLite current_timestamp'<\/span>\nWHERE<\/span>\n  id<\/span> = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, retrieve data from the notes<\/code> table:<\/p>\n\n\n
SELECT * FROM notes;<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
id | note                            | created_at          | updated_at\n---+---------------------------------+---------------------+--------------------<\/span>\n1  | Learn SQLite current_timestamp  | 2024-04-12 12:14:46 | 2024-04-12 12:17:32\n\n(1 row)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the updated_at<\/code> column is updated to the time when the row was updated.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the current_timestamp<\/code> function to get the current date and time in UTC.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Fourth, update<\/a> the row with id 1:<\/p>\n\n\n
Third, create a conditional trigger<\/a> that is invoked when a row in the notes table is updated, and the updated_at<\/code> column is not the same as the current date and time:<\/p>\n\n\n
Second, insert a row <\/a>into the notes<\/code> table:<\/p>\n\n\n