PostgreSQL: Using CURRENT_DATE and CURRENT_TIME Functions

Introduction

PostgreSQL is a powerful open-source relational database system known for its robustness, extensibility, and compliance with SQL standards. In the realm of databases, managing dates and times is a fundamental requirement as they are intrinsic to tracking events, record timestamps, and scheduling tasks. PostgreSQL offers a variety of built-in functions and operators to handle date and time values efficiently, among which the CURRENT_DATE and CURRENT_TIME functions are widely used.

Use Cases

The CURRENT_DATE and CURRENT_TIME functions are designed to retrieve the current date and time based on the server’s local time zone. CURRENT_DATE returns the current date, and CURRENT_TIME returns the current time without a date component. These functions are useful when you need to insert or compare the current date/time into your queries or tables.

Syntax and Parameters

In PostgreSQL, the CURRENT_DATE and CURRENT_TIME functions do not require any parameters and are used simply as shown:

CURRENT_DATE
CURRENT_TIME

The CURRENT_DATE function returns a date in the format YYYY-MM-DD, and the CURRENT_TIME function returns time in the format HH:MM:SS.

Both CURRENT_DATE and CURRENT_TIME are long-standing functions in SQL standards and have been supported by PostgreSQL for many versions, making them a stable and consistent feature for time-related tasks in SQL scripts and applications.

Examples Usage

Example 1: Inserting Current Date and Time

A common usage scenario for the CURRENT_DATE and CURRENT_TIME functions is to automatically insert the current date and time into a table whenever a new record is created.

CREATE TABLE events (
  id serial PRIMARY KEY,
  event_name VARCHAR (50),
  event_date DATE DEFAULT CURRENT_DATE,
  event_time TIME DEFAULT CURRENT_TIME
);

INSERT INTO events (event_name) VALUES ('Conference');

Example 2: Filtering with CURRENT_DATE

Retrieve records from a table where events are set to occur today.

SELECT *
FROM events
WHERE event_date = CURRENT_DATE;

Example 3: Time-Based Greetings

Customize greetings for users based on the current server time.

SELECT
  CASE
     WHEN CAST(CURRENT_TIME AS TIME) BETWEEN '05:00:00' AND '12:00:00' THEN 'Good morning!'
     WHEN CAST(CURRENT_TIME AS TIME) BETWEEN '12:00:01' AND '18:00:00' THEN 'Good afternoon!'
     ELSE 'Good evening!'
  END AS greeting;

Conclusion

The CURRENT_DATE and CURRENT_TIME functions in PostgreSQL offer a simple yet powerful mechanism to deal with real-time data without the complexity of time zone conversions or the use of external functions. Tapping into these functions equips developers and database administrators with tools to directly include live date and time into queries, helping to ensure accurate and up-to-date data is being used and recorded.