DATE/TIME Datatypes
There are 4 main ways to store date values in a PostgreSQL database:
DATA TYPE | DESCRIPTION | EXAMPLE | OUTPUT |
|---|---|---|---|
| TIMESTAMP | DESCRIPTION date and time | EXAMPLE | OUTPUT 2023-04-10T10:39:37 |
| DATE | DESCRIPTION date (no time) | EXAMPLE | OUTPUT 2023-04-10 |
| TIME | DESCRIPTION time (no day) | EXAMPLE | OUTPUT 10:39:37 |
| INTERVAL | DESCRIPTION interval between two date/times | EXAMPLE | OUTPUT 1 day, 2:00:10 |
We’ll go over more about each of these.
Date string formatting
Dates in a database aren’t stored as strings, but we input and fetch data from it as if it were a string with the following format for the information:
YYYY-MM-DDHH:MM:SSwhere the letters stand forYear,Month,Day,Hour,Minutes andSeconds. Let’s say for example that we want to record that we got a new user on April 10, 2023 at exactly 10:39. To represent that exact date and time we would use the format:
2023-04-10 10:39:00
TODO: this format is also supported: January 8 04:05:06 1999 PST
To get some familiarity try creating and SELECTing a few TIMESTAMPS below. I was born on May 1st, 1983 at exactly 4:00am. Can you fetch that timestamp?
SELECTTIMESTAMP'2023-04-1010:39:37';We’re just going to jump in here. We need to use a different table as none of the previous ones we’ve been using have had date fields in them. Another table available to us in chinook isemployees. Let’s get familiar with what columns are in this table by looking at the first few rows. Note that there are several columns so you may have to scroll right to see all of the data:
SELECT*FROMemployeesLIMIT3;Eachemployeehas two TIMESTAMP columns, one for theirbirth_dateand one for theirhire_date. You can use all of the ORDERing, GROUPing and other functions we learned for other columns on DATE columns as well. Try getting a list of the 4 youngestemployeesin the company.
Formatting Dates to strings
Often you don’t want to show the full raw TIMESTAMP, but rather a nicely formatted, potentially truncated version. For example, let’s say we want to get a list of theemployeesnames and the year that they were hired. To do so we’ll need to parse thehired_dateto just pull out the year. We can do so with the TO_CHAR function which works as follows
TO_CHAR([datetype],[pattern])where [date type] is a column or value of any of the above listed date/time data types, and [pattern] is a string indicating how to format the output date. The main symbols you’ll want to use to create your format patterns are here
PATTERN | DESCRIPTION | EXAMPLE | OUTPUT |
|---|---|---|---|
| HH | DESCRIPTION Hour (01-12) | EXAMPLE | OUTPUT 04 |
| HH24 | DESCRIPTION Hour (01-24) | EXAMPLE | OUTPUT 16 |
| MI | DESCRIPTION Minute | EXAMPLE | OUTPUT 15 |
| SS | DESCRIPTION Seconds | EXAMPLE | OUTPUT 23 |
| am | DESCRIPTION displays whether time is am or pm | EXAMPLE | OUTPUT am |
| YY | DESCRIPTION last 2 digits of the Year | EXAMPLE | OUTPUT 23 |
| YYYY | DESCRIPTION 4 digits of the Year | EXAMPLE | OUTPUT 2023 |
| MM | DESCRIPTION Month # of the year | EXAMPLE | OUTPUT 04 |
| Month | DESCRIPTION written Month of the year capitalized | EXAMPLE | OUTPUT April |
| Mon | DESCRIPTION abbreviated of Month of year | EXAMPLE | OUTPUT Apr |
| DD | DESCRIPTION Day # of the month | EXAMPLE | OUTPUT 10 |
| Day | DESCRIPTION written Day of the week | EXAMPLE | OUTPUT Monday |
| Dy | DESCRIPTION abbreviated Day of the week | EXAMPLE | OUTPUT Mon |
| WW | DESCRIPTION Week # of the year | EXAMPLE | OUTPUT 15 |
| Q | DESCRIPTION Quarter of the year | EXAMPLE | OUTPUT 2 |
| TZ | DESCRIPTION TimeZone | EXAMPLE | OUTPUT UTC |
The above patterns can be string together to get the format you eventually want. Some common outputs are:
SELECTTO_CHAR(TIMESTAMP'2023-04-1010:39:37','Day,MonthDDYYYY');and
SELECTTO_CHAR(TIMESTAMP'2023-04-1010:39:37','YYYY-MM-DDHH:MI:SS');and
SELECTTO_CHAR(TIMESTAMP'2023-04-1010:39:37','MM/DD/YY');You don’t have to memorize these (it’s hard to!). It’s just good to get familiar with how it works and then reference back to it when you need it in the future.
Number formatting
There are a couple of extra tools you can use on patterns that output numbers.
FORMATTER | DESCRIPTION | EXAMPLE | OUTPUT |
|---|---|---|---|
| FM | DESCRIPTION Fill Mode will remove any 0’sat the front of a 2 digit number. | EXAMPLE | OUTPUT 5 |
| th | DESCRIPTION adds the ordinal suffixeslike st, nd or th to the end of a number | EXAMPLE | OUTPUT 05th |
And of course you can combine the two to get
SELECTTO_CHAR(DATE'2023-04-03','MonthFMDDth');
String Formatting
For string outputs, most of the patterns above support different casing output based on the case you use for the pattern. Some examples using different casings of “Day”:
SELECT
TO_CHAR(DATE'2023-04-10','DAY')AS"DAY",
TO_CHAR(DATE'2023-04-10','Day')AS"Day",
TO_CHAR(DATE'2023-04-10','day')AS"day";And you can see the following common date format in UPPERCASE, Capitalized and lowercase formats:
SELECT
TO_CHAR(TIMESTAMP'2023-04-1010:39:37','FMHH:MMAMDAY,MONTHDDTHYYYY')AS"UPPERCASED",
TO_CHAR(TIMESTAMP'2023-04-1010:39:37','FMHH:MMamDay,MonthDDthYYYY')AS"Capitalized",
TO_CHAR(TIMESTAMP'2023-04-1010:39:37','FMHH:MMamday,monthFMDDthYYYY')AS"lowercased";Note that the case for numeric values doesn’t change. Still use DD for the day # of the month and YYYY for year.
We’re going to move on in the tutorial but if you’d like more details checkout thefull list of PostgreSQL date formatting functions.
Current DATE and TIME Functions
PostgreSQL supports a number of special values, or functions to help bet the current DATE, TIMESTAMP or TIME. The most used ones are
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMPand they are used by just putting them in the query
SELECTCURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP;
GROUPing BY DATE
In analytic queries, it’s very common to group things by dates. For example you may want to see new users by year, month, week or day. To do so, you’ll want to use the TO_CHAR function to convert the dates into a truncated string before you GROUP BY it. You don’t want to simply GROUP BY the raw date as those are accurate down to the millisecond so grouping by the unaltered date would be like making GROUPs for each millisecond.
The following examples are using thehire_datefield from theemployeestable and show a lot of common formats you can use for these groups. These are what we use at Chartio for ourdate group formatting standards.
GROUP PERIOD | EXAMPLE SQL | EXAMPLE OUTPUT |
|---|---|---|
| SECOND | EXAMPLE SQL | EXAMPLE OUTPUT 2018-03-04T00:00:00 |
| Minute | EXAMPLE SQL | EXAMPLE OUTPUT 2018-08-14T00:00 |
| Hour | EXAMPLE SQL | EXAMPLE OUTPUT 2018-01-02T00 |
| Day | EXAMPLE SQL | EXAMPLE OUTPUT 2003-10-17 |
| Week | EXAMPLE SQL | EXAMPLE OUTPUT 2002-W33 |
| Month | EXAMPLE SQL | EXAMPLE OUTPUT 2002-05 |
| Quarter | EXAMPLE SQL | EXAMPLE OUTPUT 2003-Q2 |
| Year | EXAMPLE SQL | EXAMPLE OUTPUT Y2012 |
| Hour of Day | EXAMPLE SQL | EXAMPLE OUTPUT 14 |
| Day of Week | EXAMPLE SQL | EXAMPLE OUTPUT Thursday |
| Day of Month | EXAMPLE SQL | EXAMPLE OUTPUT 17 |
| Day of Year | EXAMPLE SQL | EXAMPLE OUTPUT 125 |
| Month of Year | EXAMPLE SQL | EXAMPLE OUTPUT October |
Feel free to try out any of the above formats on the query below:
SELECTTO_CHAR(hire_date,'"Y"YYYY')AS"YearHired",
COUNT(*)FROMemployees
GROUPBY"YearHired";There are only 8employeesin our database so we’re not dealing with too many groups there. You can get a little more granular with theinvoicestable and it’sinvoice_datecolumn with 250 rows.
SELECTTO_CHAR(invoice_date,'"Y"YYYY')AS"YearInvoiced",
COUNT(*)FROMinvoices
GROUPBY"YearInvoiced";The above query returns the number ofinvoicescreated per year. Can you modify it to get a SUM of thetotalamount invoiced by month?
Written by:Dave Fowler
Reviewed by:Matt David
Next Topic
As a seasoned database professional, I bring years of hands-on experience in working with PostgreSQL databases. I've developed complex queries, optimized performance, and delved deep into various data types and their manipulations. Now, let's explore the concepts discussed in the provided article on date/time data types and formatting in PostgreSQL.
1. Date/Time Data Types:
In PostgreSQL, there are four main ways to store date values:
-
TIMESTAMP:
- Description: Represents date and time.
- Example:
TIMESTAMP '2023-04-10 10:39:37' - Output:
2023-04-10T10:39:37
-
DATE:
- Description: Represents date only (no time).
- Example:
DATE '2023-04-10' - Output:
2023-04-10
-
TIME:
- Description: Represents time only (no day).
- Example:
TIME '10:39:37' - Output:
10:39:37
-
INTERVAL:
- Description: Represents an interval between two date/times.
- Example:
INTERVAL '1 day 2 hours 10 seconds' - Output:
1 day, 2:00:10
2. Date String Formatting:
Dates in a PostgreSQL database are not stored as strings, but you input and fetch data as if they were strings. The common format is YYYY-MM-DDHH:MM:SS. For example, to represent April 10, 2023, at 10:39, you use 2023-04-10 10:39:00. Another supported format is January 8 04:05:06 1999 PST.
To fetch your birth timestamp, you can use:
SELECT TIMESTAMP '1983-05-01 04:00:00';3. Formatting Dates to Strings:
The TO_CHAR function is used to format date and time values. Some patterns include:
HH,HH24: Hour (01-12) and Hour (01-24).MI: Minute.SS: Seconds.am: Displays whether time is AM or PM.YY,YYYY: Last 2 digits and 4 digits of the Year.MM: Month number.Month: Written month of the year.Mon,Dy: Abbreviated month and day.DD: Day number.Day: Written day of the week.WW: Week number.Q: Quarter of the year.TZ: Timezone.
4. Number Formatting:
For numbers, there are additional tools like FM (Fill Mode) and suffixes like th. For example:
SELECT TO_CHAR(DATE '2023-04-05', 'FMDD') AS Output; -- Outputs: 5
SELECT TO_CHAR(DATE '2023-04-05', 'FMDD') || 'th' AS Output; -- Outputs: 5th
**5. String Formatting:**
Patterns support different casing. Examples with "Day":
```sql
SELECT TO_CHAR(DATE '2023-04-10', 'DAY') AS UPPERCASED,
TO_CHAR(DATE '2023-04-10', 'Day') AS Capitalized,
TO_CHAR(DATE '2023-04-10', 'day') AS lowercased;6. Current DATE and TIME Functions:
PostgreSQL supports special values/functions for the current date, timestamp, and time:
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;7. GROUPing BY DATE:
In analytic queries, it's common to group by dates. Use TO_CHAR to convert dates into truncated strings. Examples:
- GROUP BY Periods:
- Second, Minute, Hour, Day, Week, Month, Quarter, Year, Hour of Day, Day of Week, Day of Month, Day of Year, Month of Year.
Example Query:
SELECT TO_CHAR(hire_date, '"Y"YYYY') AS "YearHired", COUNT(*)
FROM employees
GROUP BY "YearHired";Feel free to explore more date group formatting standards using different periods in the provided examples.
