The Ultimate Beginner’s Guide to PostgreSQL Date and Datetime Values
The Essential Information For Working With Data and Datetime Values
Date and datetime types in SQL are a common sticking point for aspiring engineers and analysts. In this article, I’ll explain the critical date and datetime concepts needed to successfully work with these values.
By the end, you’ll be less confused and more capable. So let’s dive in!
The Basics: Dates Vs. Datetimes and ISO Formatting
Let’s start with the difference between dates and datetime values.
A date represents a specific day on the calendar. And a datetime represents a specific day on the calendar, plus the time of day.
Typically, when writing SQL, people express dates in the International Organization for Standardization (ISO) format: YYYY-MM-DD. So the date value for January 1st, 2023, looks like this when using the ISO format, ‘2023–01–01’.
Datetimes are formatted similarly, adding the time intervals after the date. The full format looks like this: YYYY-MM-DD HH:MI:SS. The “time” portion of the value is broken down by hours, minutes, and seconds.
