SQL CURRENT_TIMESTAMP

Summary: in this tutorial, you will learn how to get the current date and time by using the SQL CURRENT_TIMESTAMP function.

To get the current date and time of the database server, you use the SQL CURRENT_TIMESTAMP function as shown below:

CURRENT_TIMESTAMP
Code language: SQL (Structured Query Language) (sql)

The CURRENT_TIMESTAMP function is a SQL-standard function supported by almost all database systems such as DB2, Firebird, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, and SQLite.

The following statement returns the current date and time of the database server:

SELECT CURRENT_TIMESTAMP 
        AS 'Current date and time';
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Current date and time
-----------------------
2018-07-21 18:12:42.023
Code language: SQL (Structured Query Language) (sql)

Note that in Oracle, you need to add the FROM dual clause as shown below:

SELECT 
    CURRENT_TIMESTAMP 
        AS 'Current date and time'
FROM 
    dual;
Code language: SQL (Structured Query Language) (sql)

The CURRENT_TIMESTAMP is often used to set a default value for a DATETIME or a TIMESTAMP column of a table.

For example, the following statement creates a new table that has the created_at column which accepts the date and time as the default value.

CREATE TABLE posts(
    id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
Code language: SQL (Structured Query Language) (sql)

Whenever you insert a row into the posts table without specifying the value for the created_at column, the database system will use the current date and time for that column as shown in the following statement:

INSERT INTO posts(id,title)
VALUES(1,'SQL CURRENT_TIMESTAMP test');
Code language: SQL (Structured Query Language) (sql)

Let’s check the content of the posts table:

SELECT * FROM posts;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

In this tutorial, you have learned how to use the SQL CURRENT_TIMESTAMP function to get the current date and time of the database server.