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:
where the letters stand for Year, Month, Day, Hour, Minutes and Seconds. 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:
SELECT * FROM employees LIMIT 3;
7
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?
SELECT TIMESTAMP '2023-04-10 10: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 is employees. 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 * FROM employees LIMIT 3;
Each employee has two TIMESTAMP columns, one for their birth_date and one for their hire_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 youngest employees in 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 the employees names and the year that they were hired. To do so we’ll need to parse the hired_date to just pull out the year. We can do so with the TO_CHAR function which works as follows
TO_CHAR[[date type], [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
SELECT * FROM employees LIMIT 3;
8
OUTPUT
04
HH24
DESCRIPTION
Hour [01-24]
EXAMPLE
SELECT * FROM employees LIMIT 3;
9
OUTPUT
16
MI
DESCRIPTION
Minute
EXAMPLE
TO_CHAR[[date type], [pattern]]
0
OUTPUT
15
SS
DESCRIPTION
Seconds
EXAMPLE
TO_CHAR[[date type], [pattern]]
1
OUTPUT
23
am
DESCRIPTION
displays whether time is am or pm
EXAMPLE
TO_CHAR[[date type], [pattern]]
2
OUTPUT
am
YY
DESCRIPTION
last 2 digits of the Year
EXAMPLE
TO_CHAR[[date type], [pattern]]
3
OUTPUT
23
YYYY
DESCRIPTION
4 digits of the Year
EXAMPLE
TO_CHAR[[date type], [pattern]]
4
OUTPUT
2023
MM
DESCRIPTION
Month # of the year
EXAMPLE
TO_CHAR[[date type], [pattern]]
5
OUTPUT
04
Month
DESCRIPTION
written Month of the year capitalized
EXAMPLE
TO_CHAR[[date type], [pattern]]
6
OUTPUT
April
Mon
DESCRIPTION
abbreviated of Month of year
EXAMPLE
TO_CHAR[[date type], [pattern]]
7
OUTPUT
Apr
DD
DESCRIPTION
Day # of the month
EXAMPLE
TO_CHAR[[date type], [pattern]]
8
OUTPUT
10
Day
DESCRIPTION
written Day of the week
EXAMPLE
TO_CHAR[[date type], [pattern]]
9
OUTPUT
Monday
Dy
DESCRIPTION
abbreviated Day of the week
EXAMPLE
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'Day, Month DD YYYY'];
0
OUTPUT
Mon
WW
DESCRIPTION
Week # of the year
EXAMPLE
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'Day, Month DD YYYY'];
1
OUTPUT
15
Q
DESCRIPTION
Quarter of the year
EXAMPLE
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'Day, Month DD YYYY'];
2
OUTPUT
2
TZ
DESCRIPTION
TimeZone
EXAMPLE
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'Day, Month DD YYYY'];
3
OUTPUT
UTC
The above patterns can be string together to get the format you eventually want. Some common outputs are:
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'Day, Month DD YYYY'];
and
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'YYYY-MM-DD HH:MI:SS'];
and
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10: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
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'Day, Month DD YYYY'];
4
OUTPUT
5
th
DESCRIPTION
adds the ordinal suffixeslike st, nd or th to the end of a number
EXAMPLE
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'Day, Month DD YYYY'];
4
OUTPUT
05th
And of course you can combine the two to get
SELECT TO_CHAR[DATE '2023-04-03', 'Month FMDDth'];
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-10 10:39:37', 'FMHH:MMAM DAY, MONTH DDTH YYYY'] AS "UPPERCASED",
TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'FMHH:MMam Day, Month DDth YYYY'] AS "Capitalized",
TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'FMHH:MMam day, month FMDDth YYYY'] 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 the full 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_TIMESTAMP
and they are used by just putting them in the query
SELECT * FROM employees LIMIT 3;
0
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 the hire_date field from the employees table and show a lot of common formats you can use for these groups. These are what we use at Chartio for our date group formatting standards.
GROUP PERIOD
EXAMPLE SQL
EXAMPLE OUTPUT
SECOND
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'Day, Month DD YYYY'];
6
EXAMPLE OUTPUT
2018-03-04T00:00:00
Minute
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'Day, Month DD YYYY'];
7
EXAMPLE OUTPUT
2018-08-14T00:00
Hour
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'Day, Month DD YYYY'];
8
EXAMPLE OUTPUT
2018-01-02T00
Day
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'Day, Month DD YYYY'];
9
EXAMPLE OUTPUT
2003-10-17
Week
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'YYYY-MM-DD HH:MI:SS'];
0
EXAMPLE OUTPUT
2002-W33
Month
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'YYYY-MM-DD HH:MI:SS'];
1
EXAMPLE OUTPUT
2002-05
Quarter
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'YYYY-MM-DD HH:MI:SS'];
2
EXAMPLE OUTPUT
2003-Q2
Year
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'YYYY-MM-DD HH:MI:SS'];
3
EXAMPLE OUTPUT
Y2012
Hour of Day
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'YYYY-MM-DD HH:MI:SS'];
4
EXAMPLE OUTPUT
14
Day of Week
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'YYYY-MM-DD HH:MI:SS'];
5
EXAMPLE OUTPUT
Thursday
Day of Month
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'YYYY-MM-DD HH:MI:SS'];
6
EXAMPLE OUTPUT
17
Day of Year
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'YYYY-MM-DD HH:MI:SS'];
7
EXAMPLE OUTPUT
125
Month of Year
EXAMPLE SQL
SELECT TO_CHAR[TIMESTAMP '2023-04-10 10:39:37', 'YYYY-MM-DD HH:MI:SS'];
8
EXAMPLE OUTPUT
October
Feel free to try out any of the above formats on the query below:
SELECT * FROM employees LIMIT 3;
1
There are only 8 employees in our database so we’re not dealing with too many groups there. You can get a little more granular with the invoices table and it’s invoice_date column with 250 rows.
SELECT * FROM employees LIMIT 3;
2
The above query returns the number of invoices created per year. Can you modify it to get a SUM of the total amount invoiced by month?
How to get top 10 values in PostgreSQL?
Postgres does have a way to select the top N rows from an SQL query, but the syntax does not use the top keyword. Instead, the limit keyword must be used. PostgreSQL also gives the ability to limit the number of rows with an offset from the first row. That can be achieved using the limit N offset Y syntax.
How to get data for each month in SQL?
You usually want to take into account both the year and the month of the date, i.e. you want to group events in January 2022 separately from events in January 2021. To group data by month in SQL Server, use the DATEPART[] function. It extracts the given part [year, month, etc.] from the date.
How to get data of specific month in PostgreSQL?
In Postgres, you can use the EXTRACT[], DATE_TRUNC[], and DATE_PART[] function to extract the month from a date field and then use the GROUP BY clause to group the results by month. This blog will show you how to group the table's data by month in Postgres.
How to count records by month in PostgreSQL?
Similarly, to group data by month, you can use the DATE_TRUNC function with 'month' as the first argument. For example: SELECT DATE_TRUNC['month', date] AS month, SUM[quantity] FROM sales GROUP BY month; This query will return a list of months along with the total quantity sold each month.