Introduction to date and time data types

1. Introduction to date and time data types

Hello. My name is Jasmin, and I'll be your instructor today. In this video, we will learn about date and time data types within PostgreSQL.

2. Date and time data types

PostgreSQL has many different date, time, and datetime data types. These include, a DATE type to reflect date data only, a TIME type to reflect time data without any time zone information, a TIMESTAMP type that combines date and time data without time zones, a TIMESTAMPTZ type that is TIMESTAMP with the addition of time zones, and an INTERVAL type that includes time data between two points.

3. Date and time values

Each of these data types supports different formatting of date and time. The recommended standard for DATE is year, month, day, also known as the ISO 8601 international standard. For TIME it is hours, minutes, and seconds where seconds can be broken down further by using a decimal. The hours also accept both 12 and 24-hour values. TIMESTAMP combines the two, and to include a timezone we use TIMESTAMPTZ and add a plus or minus and the number of hours we need to offset from the Coordinated Universal Time (UTC), or the time zone abbreviation. For example, Central European Time would be plus zero one. INTERVAL can take many different inputs, here is one example where the interval is one day, two hours, 30 minutes, and four seconds.

4. Date and time in a table

Let’s create a new table with fields that use these data types. We can call CREATE TABLE and add the field name and data type as shown here for DATE, TIMESTAMP, TIMESTAMPTZ, and INTERVAL. We can add the values by calling INSERT INTO, specifying the name of the table and the fields we are adding to, and call VALUES specifying the values to be added to those fields. Note that we are inputing the date in word form, and we've used the letter abbreviation for the Eastern Standard Time time zone.

5. Date and time in a table

Here is the table. The date_info field converted into the numeric year, month, day format. The time_info field remained the same. The time with zone field changed to reflect the Eastern Standard Time zone. Note that DataCamp's database is set to Central European Time so this field tells us our input of six pm Eastern Standard Time is midnight in Central Europe. The plus one denotes the offset from UTC, since Central Europe time is one hour ahead of the UTC. Lastly, the interval length field shows the interval we specified.

6. Partial or incorrect data

If we insert a row but do not set some of the values, those missing values are set to NULL. To demonstrate, let's add another row but only provide values for one of the fields. We see the output is that the other fields have had NULL values added.

7. Partial or incorrect data

Here's another example. Let's say our field was TIMESTAMP type instead of TIMESTAMPTZ and we accidentally provided a time zone value. Since TIMESTAMP does not include the time zone information, it will be ignored as we can see here.

8. Unix time

We may encounter an occasion where time is stored in Unix time, which is the number of seconds that have elapsed since the Unix epoch. The Unix epoch is a particular point in time, and in computing it is January 1 1970, UTC. Here is an example of what Unix time looks like. In English, that date is January 3 2017!

9. Let's practice!

Let's practice working with date and time data types.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.