The PopSQL website also has some good examples of how to get different date ranges using PostgreSQL.
When using the INTERVAL, DATE_PART, or DATE_TRUNC functions, here are the commonly-used date ranges:
Last 30 Days:
WHERE "date_column" >= date_trunc('DAY',NOW()) - INTERVAL '30 DAYS'
Start of Month:
WHERE DATE_TRUNC('month', "date_column")
Only last week:
WHERE "date_column" = date_trunc('WEEK', NOW()) - INTERVAL '1 week'
Only last month:
WHERE DATE_TRUNC('MONTH', "date_column") = date_trunc('MONTH', NOW()) - INTERVAL '1 months';
Prior month (the month before last):
You can change the last
2 months to be any number of months back.
WHERE DATE_TRUNC('MONTH', "date_column") = date_trunc('MONTH', NOW()) - INTERVAL '2 months';
Last month to now:
WHERE "date_column" >= date_trunc('MONTH', NOW()) - INTERVAL '1 months'
Only last year:
You can change the last
1 year to be any number of years back.
WHERE DATE_TRUNC('YEAR', "date_column") = date_trunc('YEAR', NOW()) - INTERVAL '1 year';
Current month, last year:
WHERE DATE_TRUNC('MONTH', "date_column") = date_trunc('MONTH', NOW()) - INTERVAL '1 year'
Last year to date (to the same day last year):
WHERE DATE_TRUNC('day', "date_column") <= DATE_TRUNC('day', "now"()) - INTERVAL '1 year'
AND DATE_TRUNC('year', "date_column") = DATE_TRUNC('year', "now"()) - INTERVAL '1 year'
WHERE "date_column" between (date_trunc('year', now() - interval '1 Year')) and (now() - interval '1 year');
Find the Difference Between Two Dates Using Date_Part
Most commonly used to calculate the number of days between two dates.
DATE_PART('day', "end_date" - "start_date") as "days_between"
"end_date"::DATE - "start_date"::DATE AS "days_between"
Convert a Decimal Time to MM:SS
If you are getting a time duration (like the "Average Session Duration" from Google Analytics, but it comes in as a decimal (like 1.50 = 1:30) then use this SQL:
to_char(to_timestamp("time_duration_column"), 'MI:SS') AS "Time Duration"
Used to create a substring, or a shortened string. Most commonly used to shorten a date timestamp to YYYY-MM-DD or YYYY-MM.
substring("columnheader",[start position],[length]) as "columnheader"
substring("orderDate",1,10) as "newDate"
^ This function would take the data, start at the first character and go 10 spaces. Usually the length needed to trim a timestamp date to just the date. 2018-05-09T00:10:54+00:00 to 2018-05-09
substring("datecolumn",1,7) as "SalesMonth"
^ This function would take the data, start at the first character or number and go 7 spaces. Usually the length needed to trim 2018-08-13 to 2018-08 or YYYY-MM-DD to YYYY-MM
CASE statements are used very frequently with clients when they have certain criteria they're trying to track in their data. CASE statements allow you apply a label or perform a calculation when a certain criteria is met.
Grow recently released our tagging transform which is a non-SQL way to perform some basic CASE statements. While this will be helpful for many, you will finds some custom calculations that will require actual SQL. A CASE statement creates a new column in the dataset.
WHEN 'x happens'
THEN 'y happens'
ELSE "orignal_column_header" END AS alias
SELECT "LastName", "FirstName",
CASE "LastName" WHEN 'ashby' THEN 'villanueva'
ELSE "LastName" END as "newLastName"
^ This CASE statement will show three columns. LastName, FirstName, and then the new column which will replace the name 'ashby' with 'villanueva' at any instance. If that criteria isn't met, it will just populate the new column with the name found in the LastName column. The new column will be called "newLastName"
Things to Note About CASE Statements:
- ELSE statements are a good error check. If you CASE everything, and there are still other values, the ELSE statement would provide that value.
- When working with identifying a string, you need to wrap the string in single quotations 'jake' , 'jason' , 'sales team', for the query to identify that string exactly.
- You can use calculations in your CASE statement.
- You can pivot data by using case statements.
This statement is summing up any week end sales found in the Northwest sales region.
SUM(CASE WHEN "region" = 'Northwest' THEN "weekend_sales" END) as Northwest
GROuP BY "Date"
This statement is applying less than/greater than logic to a data field to calculate the total refund amount in the last 9 days. Probably being used to build out a basic cohort analysis.
WHEN "days_since_refund" <= 9 AND refundAmount > 0
THEN "refundAmount" END) / SUM("total_price_usd") as '0-9'
This statement is creating three different customer categories as well as including other fields from the dataset.
Enrollment_Date__c AS 'Enrollment Date',
CASE WHEN "Type" = 'Customer' THEN 1 ELSE 0 END AS "Customer",
CASE WHEN "Type" = 'Customer Cancelled' THEN 1 ELSE 0 END AS "Cancelled",
CASE WHEN "Type" = 'Customer Cancelled - Trial Period' THEN 1 ELSE 0 END AS "Trial Fallout"
Note: If you are searching for a specific part of a string, you can you can use the underscore and the percent symbol as wildcards.