Database Essentials - Predefined Functions - Dealing with Unix Timestamp or epoch

This article covers important date manipulation functions in PostgreSQL. It explains how to work with current date and timestamp, date arithmetic using intervals, getting the beginning date or time using date_trunc, extracting information using to_char function, and dealing with Unix timestamps.

Key Concepts Explanation

Getting Current Date and Timestamp

The current_date function returns today’s date, and current_timestamp returns the current timestamp. These functions do not require () and have specific formats:

  • Date format: yyyy-MM-dd
  • Timestamp format: yyyy-MM-dd HH:mm:ss.SSS
SELECT current_date AS current_date;
SELECT current_timestamp AS current_timestamp;

Date Arithmetic

Perform arithmetic operations using intervals by adding or subtracting days, months, or years from the current date.

SELECT current_date + INTERVAL '32 DAYS' AS result;
SELECT current_date - INTERVAL '730 DAYS' AS result;

Beginning Date or Time - date_trunc

Use date_trunc to get the beginning dates or times based on specified units like year, month, week, day, or hour.

SELECT date_trunc('MONTH', current_date) AS month_beginning;
SELECT date_trunc('HOUR', current_timestamp) AS hour_beginning;

Extracting Information using to_char

Retrieve specific elements such as year, month, day, day of the week from dates or timestamps using to_char.

SELECT current_timestamp, to_char(current_timestamp, 'yyyy') AS year;
SELECT current_timestamp, to_char(current_timestamp, 'DD') AS day_of_month;

Extracting Information using extract

Use extract to retrieve various date or timestamp components like century, decade, year, month, etc.

SELECT extract(month FROM current_date) AS month;
SELECT extract(hour FROM current_timestamp) AS hour;

Dealing with Unix Timestamp

Convert dates or timestamps to Unix epoch format or Unix timestamps.

SELECT extract(epoch FROM current_date) AS date_epoch;
SELECT extract(epoch FROM '2019-04-30 18:18:51'::timestamp) AS unixtime;

Hands-On Tasks

  1. Use date_trunc to get the beginning date of the week.
  2. Convert a timestamp to Unix epoch format and back to a timestamp.


In this guide, we explored essential date manipulation functions in PostgreSQL. Practice these examples and engage with the community for further learning opportunities.

Video Tutorial

Watch the video tutorial on YouTube

Remember to follow along with the tasks mentioned in the article for practical experience. Join the community to interact and share your learnings with others.

Watch the video tutorial here