Introduction to PostgreSQL timestamp Data Type

The timestamp data type is a commonly used data type in PostgreSQL for storing date and time information. It can accurately represent date and time, including year, month, day, hour, minute, second, and microsecond information.

Syntax

The syntax for the timestamp data type is as follows:

timestamp [ (p) ] [ without time zone ]

Where p represents the number of digits after the decimal point, and without time zone indicates that it does not include timezone information.

Use Cases

The timestamp data type is very useful in many scenarios, especially when precise storage and retrieval of date and time information are required. It can store year, month, day, hour, minute, second, and microsecond information, and can represent a time range from 4713 BC to 294276 AD. When designing and implementing applications that need to store time information, the timestamp data type is a very common choice.

Examples

Here are two examples of using the timestamp data type:

Example 1

Create a table named employee that includes the employee’s name and hire date:

CREATE TABLE employee (
    name VARCHAR(50),
    hire_date TIMESTAMP
);

Insert two records into the employee table:

INSERT INTO employee (name, hire_date)
VALUES
    ('Alice', '2022-01-01 09:00:00'),
    ('Bob', '2022-01-02 13:30:00');

Query the records from the employee table:

SELECT * FROM employee;

The query result is as follows:

 name  |       hire_date
-------+------------------------
 Alice | 2022-01-01 09:00:00
 Bob   | 2022-01-02 13:30:00
(2 rows)

Example 2

Query the current time:

SELECT now();

The query result is as follows:

now
-------------------------------
2023-03-13 08:16:23.424864+00
(1 row)

Conclusion

The timestamp data type is a commonly used data type in PostgreSQL for storing date and time information. It can accurately represent date and time, including year, month, day, hour, minute, second, and microsecond information. By using the timestamp data type, it is easy to store and retrieve time information in PostgreSQL. In addition, PostgreSQL also provides many built-in functions for the timestamp data type, such as extract(), date_trunc(), and to_char(), which can further facilitate the handling of time data. When designing and implementing PostgreSQL databases, using the timestamp data type is a very common choice.