Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-date-data-type/ below:

PostgreSQL - Date Data Type

PostgreSQL offers powerful DATE data type and date functions to efficiently handle date and time information. PostgreSQL DATE data type allows for storing and manipulating calendar dates while its robust set of date functions enables users to perform operations like date arithmetic and formatting.

In this article, We will learn about the Date Data Type in PostgreSQL by understanding various examples and comprehensive date and time handling.

PostgreSQL offers several date and time data types to represent and manipulate calendar dates, timestamps, and time intervals.

These data types allow us to store and query both simple date values (like 2024-10-10) and more complex ones that include time zones, timestamps, and intervals.

The common PostgreSQL date data types are:

1. DATE: Stores date values without time (e.g., '2024-10-10').
   
2. TIMESTAMP: Stores both date and time values (e.g., '2024-10-10 14:30:00').
   
3. TIMESTAMPTZ: A TIMESTAMP with a time zone.
   
4. TIME: Stores only the time (e.g., '14:30:00').
   
5. INTERVAL: Represents a time span (e.g., '1 year 2 months').

The DATE data type in PostgreSQL stores calendar dates without any associated time values. It follows the format YYYY-MM-DD (Year-Month-Day).

When we insert a date into a PostgreSQL table, we simply need to define a column with the DATE data type. PostgreSQL will handle the formatting and ensure that it’s stored correctly.

Let's create a table that stores information about employees, including their birthdates.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    birthdate DATE
);

Now, insert some date values:

INSERT INTO employees (name, birthdate)
VALUES 
('Alice', '1990-01-15'),
('Bob', '1985-07-23'),
('Charlie', '2000-03-02');

To retrieve employees born before 1990:

SELECT name, birthdate
FROM employees
WHERE birthdate < '1990-01-01';\\

Output:

• TIMESTAMP: It Stores date and time without any time zone information. It uses the format YYYY-MM-DD HH:MM.
  
• TIMESTAMPTZ: It is Similar to TIMESTAMP, but it includes time zone information. PostgreSQL will automatically convert the time to the specified time zone.

Create a table for event scheduling:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(50),
    event_start TIMESTAMP
);

Insert some events:

INSERT INTO events (event_name, event_start)
VALUES 
('Conference', '2024-12-01 09:00:00'),
('Webinar', '2024-12-05 14:00:00');

SELECT event_name, event_start
FROM events;

Output:

The TIME data type stores only the time of the day, without any date.

CREATE TABLE office_hours (
    id SERIAL PRIMARY KEY,
    day VARCHAR(50),
    open_time TIME,
    close_time TIME
);
INSERT INTO office_hours (day, open_time, close_time)

VALUES 
('Monday', '09:00:00', '17:00:00'),
('Friday', '10:00:00', '16:00:00');

Query the office hours:

SELECT day, open_time, close_time
FROM office_hours;

Output:

The INTERVAL data type represents a span of time (e.g., '2 years', '3 days').

Let's calculate the age of employees by using the INTERVAL type.

SELECT name, birthdate, AGE(birthdate) AS age
FROM employees;

Output:

The NOW() function returns the current date and time.

SELECT NOW();

Output:

The AGE() function calculates the difference between two dates.

SELECT AGE('2024-10-10', '1990-01-15') AS age_difference;

Output:

The EXTRACT() function extracts parts of a date (year, month, day).

SELECT EXTRACT(YEAR FROM birthdate) AS birth_year
FROM employees;

Output:

The TO_CHAR() function is used for formatting date and time output.

SELECT TO_CHAR(NOW(), 'DD/MM/YYYY') AS formatted_date;

Output:

• The DATE data type in PostgreSQL requires 4 bytes of storage and supports a range from 4713 BC to 5874897 AD.
  
• PostgreSQL uses the 'yyyy-mm-dd' format for storing and inserting date values.
  
• PostgreSQL supports date arithmetic, allowing you to add or subtract intervals from dates.
  
• PostgreSQL offers a variety of date functions for manipulating and extracting parts of dates. Some commonly used functions include AGE(), EXTRACT(), DATE_TRUNC(), and TO_CHAR().

Overall, PostgreSQL provides a versatile timestamp data type and advanced time zone handling, allowing for precise date-time management across different regions. Functions like NOW return the current timestamp, while TO_CHAR formats date and time values. The AGE function calculates time differences, and EXTRACT allows you to retrieve specific parts of a date or time. These powerful features make PostgreSQL a robust solution for handling complex date-time operations in any application.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4