1. Converting between date, time, and text
Let's learn about converting between different data types.
2. Format strings
First, let's look at format strings. These are templates to specify the components of dates and times. Here are some common examples for a four-digit year, a two-digit numerical month and day, a two-digit hour for a 24 and 12-hour clock - note that not specifying a number will default to a 12-hour clock, and lastly, minutes and seconds.
We'll use these a lot for conversions.
3. Convert strings into dates
The TO_DATE function converts strings into dates. It takes two arguments: the text string and the format string.
Here is a query showing various strings we will convert into date types. Notice that the two arguments map to each other. In the first argument, we have three different ways of writing January 15th, 2023. In the second argument, we specify the format string to help the query understand the different components. Selecting the wrong format string may generate incorrect data or cause an error.
We see TO_DATE has converted each of our strings into the standardized year, month, and date output.
4. Convert strings into dates
We could achieve the same result with the CAST function, which allows us to convert one data type into another.
In this case, we specify the data type. We've used DATE, but we can use any data type. Here we have the same examples we used with TO_DATE.
5. The cast operator
Another way to convert data types is using the CAST operator, denoted with two colons.
It works the same as the CAST function but is specific to PostgreSQL.
6. Convert into dates and times
Let's convert some times with the TO_TIMESTAMP function. Recall that the TIMESTAMP data type includes both date and time. The TO_TIMESTAMP function syntax is the same as TO_DATE but for date, time, and time zones.
Here it is in action. Notice we have used HH24 for a 24-hour clock.
7. Converting unix time
TO_TIMESTAMP can convert Unix time into a more readable format. We can also combine it with the AT TIME ZONE clause to specify the time zone.
8. Extracting unix time
To convert to Unix time, we would use the EXTRACT function which retrieves values. It calculates the time elapsed from the epoch to the time specified using FROM TIMESTAMP.
At noon UTC on January 3, 2017, precisely 1,483,444,800 seconds have elapsed since January 1, 1970.
9. Converting fields
We have converted individual values, but can also convert entire fields (or columns).
If our field is uniform, we can convert each text string using the same format string with TO_DATE or TO_TIMESTAMP.
10. Converting fields
If our field is varied, we need to use the CAST function.
11. Convert date or time into text
The TO_CHAR function allows us to convert in the other direction. The first argument is the date or time we wish to convert. The second argument is the format string.
Here it is in action. Notice we are changing the time into a 12-hour clock notation.
12. Verify the data types
Our converted field looks the same as our original, but when checking the datatypes we see that they are, in fact, different.
13. Different delimiters
We can also customize our text by specifying delimiters.
Here are examples with slashes, dots, and even labels as the delimiter.
14. Non-numeric text
We can also specify the non-numeric text for the conversion, as shown here for the day of the week and month. We've chosen to have the abbreviations of the day of the week and month in this output.
15. Custom formats
We can even choose which components to use! This example omits the seconds.
16. AM and PM
We can add an AM or PM in lower or upper case. Notice that we can use either AM or PM in the query and SQL will use the correct one in the output.
17. MDY format
We can change the order of the year, month, and day.
We can display the time zone or the UTC offset, too, with the addition TZ or OF.
18. Let's practice!
Let's practice.