SQLite current_time Function

Summary: in this tutorial, you will learn how to use the SQLite current_time function to get the current time in UTC.

Introduction to the SQLite current_time function

In SQLite, the current_time function allows you to get the current time in UTC with the format HH:MM:SS.

Here’s the syntax of the current_time function:

current_time

The current_time function returns the current time in UTC as a string in the format HH:MM:SS.

SQLite current_time function examples

Let’s take some examples of using the current_time function.

1) Basic SQLite current_time function example

The following statement uses the current_time function to return the current time in UTC:

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

Output:

current_time
------------
14:23:58Code language: plaintext (plaintext)

If you want to get the current local time, you can pass the result of the current_time function to the time() function and use the localtime modifier.

SELECT time(current_time, 'localtime') local_time;Code language: SQL (Structured Query Language) (sql)

Output:

local_time
----------
21:26:17Code language: plaintext (plaintext)

2) Using the current_time function as the default value of a column

In practice, you can use the current_time function as the default value of a time column.

First, create a table called user_activities to store user activities:

CREATE TABLE user_activities (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    activity_type TEXT NOT NULL,
    started_at TEXT DEFAULT current_time,
    started_on TEXT DEFAULT current_date
);Code language: PHP (php)

The user_activities table has the started_at and started_on columns with the default values are the results of the current_time and current_date functions.

Second, insert a row into the user_activities table:

INSERT INTO
  user_activities (username, activity_type)
VALUES
  ('admin', 'Signed in');Code language: JavaScript (javascript)

In the statement, we don’t specify the time and date for the started_at and started_on columns.

Third, retrieve the data from the user_activities table:

SELECT * FROM user_activities;

The following picture shows the result:

id | username | activity_type | started_at | started_on
---+----------+---------------+------------+-----------
1  | admin    | Signed in     | 14:41:09   | 2024-04-12

The output indicates that the created_at column is populated with the time at which the INSERT statement executed.

Summary

• Use the current_time function to obtain the current time in UTC.