TIMESTAMP

MySQL

Example

CREATE TABLE Employees (
   id INT PRIMARY KEY,
   name VARCHAR(20),
   registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO Employees (id, name) VALUES (1, 'John');

Output

SELECT * FROM Employees;

id	name	registered
1	John	2022-01-11 17:15:12

Explanation

The TIMESTAMP data type is used in the CREATE TABLE statement to create a new table Employees. The registered column has the TIMESTAMP data type, and its default value is set to CURRENT_TIMESTAMP. When a new row is inserted into the Employees table without specifying a value for the registered column, MySQL will automatically use the current timestamp. Therefore, when we insert ‘John’ into the Employees table without specifying a value for registered, the value is automatically set to the current timestamp.

PostgreSQL

Example

CREATE TABLE timestamps (
    event_name VARCHAR(100),
    event_time TIMESTAMP DEFAULT NOW()
);

INSERT INTO timestamps (event_name) VALUES ('Test event');

SELECT * FROM timestamps;

Output

 event_name |     event_time
------------+---------------------
 Test event | 2022-04-01 14:25:13

Explanation

In this example, a table named ‘timestamps’ is created, containing a field ‘event_name’ of type VARCHAR(100) and a field ‘event_time’ of type TIMESTAMP. The TIMESTAMP field is set to have a default value of the current timestamp, denoted by NOW().

Then, a new row is inserted into the ‘timestamps’ table, only specifying a value for ‘event_name’. As a result, the ‘event_time’ field is automatically populated with the current timestamp.

Lastly, a SELECT statement is used to display all rows in the ‘timestamps’ table. The output shows the ‘event_name’ alongside the time the event was inserted into the table, represented by ‘event_time’.

Oracle

Example

SELECT TO_TIMESTAMP('2003/07/09 12:01:02', 'YYYY/MM/DD HH:MI:SS')
FROM dual;

Output

09-JUL-03 12.01.02.000000000 PM

Explanation

This example represents a SQL query using Oracle’s TO_TIMESTAMP function. This function converts the provided string to a timestamp by following the specified format. The ‘dual’ class represents a dummy table useful for selecting expressions without referencing any actual data. The output displays the provided string converted to a timestamp.

SQLite

Example

CREATE TABLE IF NOT EXISTS Orders (
    OrderID integer PRIMARY KEY,
    OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO Orders (OrderID) VALUES (1);

SELECT * FROM Orders;

Output

OrderID   OrderDate
--------  -------------------
1         2021-08-03 14:45:25

Explanation

In this example, an Orders table is created with a TIMESTAMP column named OrderDate. The DEFAULT CURRENT_TIMESTAMP sets the default value of OrderDate to the current date and time. Then a new row is inserted into the Orders table with an OrderID of 1. When selecting all data from the Orders table, the current timestamp is displayed as OrderDate for the row with OrderID set to 1.