Date and Time Formatting: NOW(), CURRENT_DATE, DATE_PART()

Dates and time aren’t just some abstract numbers—they’re the key to a ton of valuable info in your data. In real life, you bump into dates all the time: like analyzing sales by month, filtering by employees’ birthdays, or comparing time intervals. Knowing how to handle dates lets you build more flexible queries and pull off some pretty cool analytics.

Examples where working with dates is super important:

• Analyzing sales for a specific month.
• Counting how many users registered in the last year.
• Building reports by time intervals (like monthly revenue).

PostgreSQL has a bunch of functions for working with dates, and right now we’ll check out just the most useful ones.

Main functions for working with dates and time

The NOW() function returns the current date and time for the database server. Use it when you need to know the exact current time. For example: you want to record when a new row was created.

Syntax:

SELECT NOW();

Sample result:

2023-11-05 15:23:45.123456+00

Example use: You want to insert an order with the current date and time:

INSERT INTO orders (order_id, order_date, total_amount)
VALUES (1, NOW(), 150.00);

Comment: here NOW() will automatically add the current date and time to the order_date column.

You’ll learn exactly how the INSERT operator works in the next lectures :P

The CURRENT_DATE function

If you just need the current date without the time, use CURRENT_DATE. It only gives you year, month, and day.

Syntax:

SELECT CURRENT_DATE;

Sample result:

2023-11-05

Example use: Let’s say you want to get all records for today:

SELECT *
FROM orders
WHERE order_date = CURRENT_DATE;

Comment: here we’re comparing the date from the order_date column with the current date.

Let’s add a bit of humor. NOW() is like your coffee at work: ready for you right now. And CURRENT_DATE is like the calendar on your wall: just the date, no extra details.

Extracting date parts with DATE_PART()

The DATE_PART() function lets you grab a specific part of a date, like year, month, day, hour, or minute. Super handy for stuff like counting orders for a certain year or figuring out the day of the week.

Syntax:

DATE_PART('part', date)

Example:

SELECT DATE_PART('year', NOW()) AS current_year;

Sample result:

Date parts you can extract:

• year: year.
• month: month.
• day: day.
• hour: hour.
• minute: minute.
• second: second.
• dow: day of week (0 = Sunday).

Example 2: Let’s extract the month from the current date.

SELECT DATE_PART('month', CURRENT_DATE) AS current_month;

Result:

You can use DATE_PART() for more complex calculations. For example:

You want to select only those users who were born this year:

SELECT *
FROM students
WHERE DATE_PART('year', birth_date) = DATE_PART('year', CURRENT_DATE);

Sample result:

Practical examples

Some of these examples use operators you haven’t learned yet. Don’t worry, soon you’ll be cranking out queries like these with no sweat. I just want to show you more real-life examples. And maybe tease you a bit :)

Example 1: Figuring out a user’s age

Let’s say we have a users table with each user’s birth date. We want to calculate their age.

Query:

SELECT user_id, first_name, last_name, 
       DATE_PART('year', CURRENT_DATE) - DATE_PART('year', birth_date) AS age
FROM users;

We’re just subtracting the years from the current date and the birth date. It’s a quick but not super accurate way to get age.

Sample result:

Example 2: Filtering by time

If you want to select all orders made in the last hour:

SELECT *
FROM orders
WHERE order_date >= NOW() - INTERVAL '1 hour';

Notice how handy it is to use INTERVAL (for time intervals).

Example 3: Grouping by month

You want to count the number of orders for each month of the current year:

SELECT DATE_PART('month', order_date) AS order_month, COUNT(*) AS order_count
FROM orders
WHERE DATE_PART('year', order_date) = DATE_PART('year', CURRENT_DATE)
GROUP BY DATE_PART('month', order_date)
ORDER BY order_month;

Grouping is done by month number, and the result is sorted by it too.

Sample result:

Example 4: Extracting the day of the week

You want to know which day of the week has the most orders:

SELECT DATE_PART('dow', order_date) AS day_of_week, COUNT(*) AS order_count
FROM orders
GROUP BY DATE_PART('dow', order_date)
ORDER BY order_count DESC;

The DATE_PART('dow') command will return the day of the week for each order as a number, where 0 is Sunday, 1 is Monday, and so on. DOW stands for DayOfWeek.

Sample result:

Watch out for common mistakes

Working with dates can be a headache because of mistakes. Here are some common problems you might run into:

Date and time format: when you use NOW() or any function that returns a date with time, always check its format. For example, if you compare order_date with CURRENT_DATE, make sure the time is ignored or set explicitly.

Date as a string: sometimes in databases, dates are stored as strings (like text). If you try to use date functions (like DATE_PART()), you’ll get an error. Make sure your data is of type DATE or TIMESTAMP.

Different time zones: if your server runs in one time zone and your data comes from another, it can get confusing. Consider using the TIMESTAMPTZ time zone type.