postgresql
  1. postgresql-timestamp
PreviousNext

Timestamp - (PostgreSQL Data Types)

In PostgreSQL, timestamp is a data type used to store date and time information. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points and summary of the timestamp data type in PostgreSQL.

Syntax

timestamp [(p)] [without time zone]
  • p (Optional): The number of decimal places to include in the seconds field. Defaults to 6.
  • without time zone (Optional): Indicates that the timestamp should be interpreted as local time, rather than UTC.

Example

Let's take a look at an example of using the timestamp data type in PostgreSQL.

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  start_time TIMESTAMP
);

INSERT INTO events (name, start_time) 
VALUES 
('Event 1', '2021-07-25 14:30:00'), 
('Event 2', '2021-08-01 15:00:00'), 
('Event 3', '2021-08-08 16:00:00');

Explanation

In this example, we created a table called events with three columns: id, name, and start_time. The start_time column is of type timestamp and will store the date and time each event starts.

We then inserted three rows into the events table, each with a unique name and start time.

Use

The timestamp data type in PostgreSQL is commonly used to store date and time information. It's useful for storing information about events, transactions, and other time-based data.

Important Points

  • By default, the timestamp data type in PostgreSQL stores timezone information.
  • The timezone can be ignored by using the optional without time zone modifier.
  • PostgreSQL supports a wide range of input formats for timestamp values, including ISO 8601, SQL standard, and many others.
  • timestamp values in PostgreSQL are stored as 8-byte integers, representing both date and time.

Summary

In this tutorial, we discussed the timestamp data type in PostgreSQL. We covered the syntax, example, output, explanation, use, and important points of the timestamp data type. With this knowledge, you can now use the timestamp data type in your PostgreSQL databases to store and manipulate date and time information.

TimeInterval
Published on: