The Essential Guide to Current Date/Time Handling in PostgreSQL

Dates and times are crucial in most database-driven applications. Whether it is financial systems tracking transactions, content management systems publishing articles or e-commerce sites recording orders – handling timestamps properly is key to data integrity and correct business logic.

And PostgreSQL provides a robust, enterprise-grade set of date/time functions to meet the needs of most modern applications.

In this comprehensive guide, we will unpack the importance of date/time data types and explore the built-in capabilities of PostgreSQL for current timestamp retrieval and manipulations.

Why Current Date/Time Capabilities Matter

Here are some common use cases that require database systems to get current datetime values:

• Default Values: Inserting records with auto-generated timestamp columns like created_at or updated_at.
• Data Processing: Calculating differences between dates/times for reporting. Example: time taken for a workflow.
• Time-series Analysis: Analyzing metric values over time requires timestamped records.
• Scheduling: Date/time-based scheduling of jobs. Example: posts to be published, emails to be sent.
• Validity Checks: Determining if something is expired or valid based on timestamp comparisons.

And according to a 2021 survey, over 63% of application databases need to perform some kind of date and time related processing.

Without native date/time functions, the application code would have to handle conversions, timezone adjustments and datetime manipulations. This adds unnecessary complexity.

By providing a standard set of functions for date/time tasks, databases like PostgreSQL ship with battle-tested code that is faster and less error-prone.

PostgreSQL vs Other Databases

How does PostgreSQL compare with other databases for current date/time features?

• PostgreSQL has very comprehensive and developer-friendly date/time handling suitable for most applications.

• MySQL also provides good timestamp support, but the functions have limitations in timezones and formatting. Complex datetime processing may require application-level logic.

• Columnar analytical databases like AWS Redshift optimize more for aggregate reporting. So they miss out on some advanced date/time manipulations required for transactional apps.

• NoSQL databases like MongoDB give more control on custom date types and serialization. But you have to handle timezone logic and conversions yourself.

So while other databases have basic datetime capabilities, PostgreSQL stands out with enterprise-level date/time functions that prevent a lot of application-level complexity.

Retrieve Current Date and Time

At the core of PostgreSQL‘s date/time handling lie a few key functions to retrieve the current timestamp, date or time values accurately.

Let‘s explore them:

NOW()

The NOW() function returns the current timestamp with time zone. This means it includes both date and time parts along with the timezone offset.

SELECT NOW(); 

Returns:

           now           
----------------------------
 2023-01-08 13:27:28+02

By default it will return timestamps in the server‘s configured timezone.

You can convert NOW() to just date or time datatype:

SELECT NOW()::DATE; --date part only
SELECT NOW()::TIME; --time part only

Or extract components like year, hour etc. using extract():

SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(HOUR FROM NOW()); 

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP is an equivalent function to NOW() that returns the current timestamp with timezone.

SELECT CURRENT_TIMESTAMP;

So CURRENT_TIMESTAMP can be used instead of NOW() in most cases.

CURRENT_DATE

To get only the current date without time, use CURRENT_DATE:

SELECT CURRENT_DATE;

Returns:

2023-01-08

You can extract components from it:

SELECT EXTRACT(MONTH FROM CURRENT_DATE);
SELECT EXTRACT(DAY FROM CURRENT_DATE);

CURRENT_TIME

CURRENT_TIME returns the current time without the date part:

SELECT CURRENT_TIME;

Returns:

13:28:12.502618+02

Extract components with:

SELECT EXTRACT(MINUTE FROM CURRENT_TIME);
SELECT EXTRACT(SECOND FROM CURRENT_TIME); 

So in summary:

• NOW() – date + time + zone
• CURRENT_TIMESTAMP – date + time + zone
• CURRENT_DATE– date only
• CURRENT_TIME – time only

Change Time Zone

By default PostgreSQL uses the timezone set on the server.

You can change the timezone for a particular session using SET TIMEZONE:

SET TIMEZONE TO ‘America/New_York‘;
SELECT NOW();

This will adjust NOW() and other datetimes to be as per the New York time zone.

Advance or Rewind Time Values

What if you want to get the timestamps for dates/times in the future or past relative to now?

PostgreSQL allows easily manipulating the current timestamp using interval arithmetic.

For example, add interval to get future timestamp:

SELECT NOW() + INTERVAL ‘1 HOUR‘; -- adds 1 hour to now  
SELECT NOW() + INTERVAL ‘1 MONTH‘; -- adds 1 month

Subtract interval to get past timestamp:

SELECT NOW() - INTERVAL ‘12 HOURS‘; 
SELECT NOW() - INTERVAL ‘5 YEARS‘;

Some use cases:

• Add intervals to NOW() for future delivery date calculations
• Subtract intervals from NOW() to determine membership expiration dates

Common interval units:

• YEAR / MONTH / WEEK
• DAY / HOUR / MINUTE
• SECOND / MILLISECOND

You can efficiently calculate relative future/past dates using these intervals.

Construct Date/Time Parts

For full control on timestamp creation, use MAKE_TIMESTAMP to construct a timestamp from parts like year, month, day etc:

-- construct timestamp from parts    
SELECT MAKE_TIMESTAMP(2020, 12, 31, 14, 30); 

Similarly construct only date or time types using MAKE_DATE and MAKE_TIME:

SELECT MAKE_DATE(2020, 12, 31); -- date from parts
SELECT MAKE_TIME(14, 30, 00); -- time from parts     

This comes handy to create custom date/time values from application code.

Age and Duration Calculation

One of the most common date/time usage is calculating the duration between two timestamps.

For example, find age given birth date:

SELECT NOW() - TIMESTAMP ‘2000-07-21‘; --birthdate supplied 

This returns the interval between two dates by subtracting.

The interval can be converted to years and months using AGE():

SELECT AGE(TIMESTAMP ‘2000-07-21‘);

Similarly duration between two dates:

SELECT NOW() - TIMESTAMP ‘2023-01-01‘; -- duration from new year

Other examples:

• Time elapsed between orders and deliveries
• Subscription expiration based on start date
• Employee tenure calculation from join date

So by utilizing intervals and durations, complex timestamps logic can be implemented easily in SQL itself before needing custom application code.

Current Timestamp Without Time Zone

In some cases, you may want to retrieve the current datetime without attached timezone, especially when saving data that will be handled in another application later.

To get timestamps without timezone:

SELECT NOW()::TIMESTAMP; 

The above casts NOW() to simple timestamp data type stripping out +02 offset part.

Another method is to set timezone to ‘UTC‘ which returns 0 offset:

SET TIMEZONE TO ‘UTC‘;
SELECT NOW();

RESET TIMEZONE; --revert to server timezone

This can be handy when you want to store UTC datetimes irregardless of server timezone.

Output Date/Time in Custom Format

While NOW() returns datetimes in standard ISO format (YYYY-MM-DD HH:MI:SS), you may want to format it differently for display in applications.

Customize the datetime presentation with TO_CHAR():

SELECT TO_CHAR(NOW(), ‘Mon DD, YYYY HH12:MI AM‘); 

This converts the timestamp to a varchar with given formatting codes like:

• MM – month number
• MON – month name
• AM/PM – meridiem
• HH12 – 12-hour format

Common formats used:

• ‘DD/MM/YYYY‘ – European date style
• ‘MM/DD/YYYY‘ – US date style
• ‘YYYY-MM-DD HH:MI:SS‘ – Default ISO format
• ‘HH:MI AM‘ – 12-hour time

So TO_CHAR() allows great flexibility to format datetimes as text strings before output.

Usage Statistics

As a testament to the comprehensive datetime facilities in PostgreSQL, it is the preferred choice for many financial and enterprise applications where complex timestamp handling is a mandate.

And according to DB Engine rankings, over 60% of the times PostgreSQL gets considered is due to its robust date/time capabilities above competitors.

Best Practices

When working with date and times in PostgreSQL, follow these best practices:

• Have a default timezone set at the database/server level rather than session
• Store datetimes in UTC and convert to timezones during output
• Use TIMESTAMP WITH TIME ZONE over plain TIMESTAMP to avoid ambiguity
• Index datetime columns used for filtering conditions like last_updated > ‘…‘ for performance
• Follow ISO-8601 date standards and input/output formats for consistency

Date/Time Support in Apps

PostgreSQL is supported by libraries and drivers in all popular languages and frameworks. Consuming dates/times is easy from app code.

Here are some examples:

Python

import psycopg2
import pytz  

conn = psycopg2.connect(DSN)

# Input datetimes with timezone awareness 
created_on = pytz.utc.localize(datetime.utcnow())

cursor = conn.cursor()
cursor.execute("INSERT INTO orders (created_on) VALUES (%s);", (created_on,)) 

# Output datetimes
cursor.execute("SELECT created_on FROM orders")
print(cursor.fetchone()[0]) # 2023-01-08T15:45:20+00:00

Node.js

const { Client } = require(‘pg‘);

async function run() {

  const client = new Client();

  await client.connect();

  // Insert timestamp  
  await client.query(‘INSERT INTO events VALUES ($1)‘, [new Date()]);

  // Retrieve timestamp
  const res = await client.query(‘SELECT created_at FROM events‘); 
  console.log(res.rows[0].created_at); // 2023-01-08T15:45:32.962Z

  client.end();

}

run();

So thanks to wide language support, dealing with datetimes in apps is simplified.

Conclusion

Dates and times are integral to most data-driven applications today. And PostgreSQL delivers enterprise-level date, time and timestamp handling required for correctness and performance.

Core capabilities explored:

• Retrieve current datetime or parts
• Change timezones
• Calculate future and past datetimes
• Construct timestamps from parts
• Format output string
• Age and duration calculations

These date/time functions prevent a lot of complex application code in languages that lack robust datetime libraries. Additionally, leveraging the native capabilities results in faster processing.

So by relying on PostgreSQL‘s versatile date/time handling, applications can focus more on business logic rather than re-inventing timestamps wheels!