Data Analysis Journal

Share this post

SQL Date-Time Cheatsheet

dataanalysis.substack.com
SQL

SQL Date-Time Cheatsheet

A short guide to steps, solution, and must-know terminology

Olga Berezovsky
Dec 23, 2020
5
Share this post

SQL Date-Time Cheatsheet

dataanalysis.substack.com

Working with timestamps and data formats in SQL can be tricky. Often dates are loaded and stored as strings, var, or else in databases. And yet, date-time values are the most common data manipulations in SQL. Below I’ll cover 3 important must-know date functions like DATEPART(), DATEADD(), DATEDIFF(),  which often are asked during SQL coding interviews, and will share some handy date extraction solutions or formattings I applied in my sql for data analysis over last month. 

DATEPART() or DATE_PART()

DATEPART() - retrieves day, month, or year from the dataset: 

DATEPART (datepart , date)

Example:

SELECT DATEPART(MONTH, created_at) AS MonthCreated;

SELECT DATEPART(WEEKDAY, created_at) as WeekDayCreated

DATEADD()

DATEADD() - adds or subtracts a specific period from a given date. You should use it when you want to add or subtract, for example, 10 days/months from the current date.

DATEADD (datepart, number, date)

Example:

SELECT DATEADD(day, 28, '2020/01/25') AS DateAdd;

SELECT DATEADD(month, -2, '2020/09/25') AS DateAdd;

You can also apply it as a condition for a JOIN: 

FROM item

LEFT OUTER JOIN order 

ON order.item_id = item.id

AND DATEADD(day,7,order.created_at::DATE) = item.created_at::DATE

DATEDIFF()

DATEDIFF() - returns the difference in days, months, years, or hours between two dates.

You should use it to find out how many days have passed between two dates.

DATEDIFF ( datepart , startdate , enddate )

Example:

SELECT DATEDIFF(MONTH,'September','November') AS MonthsDiff

SELECT DATEDIFF(WEEK, '2020-05-01','2020-09-31') AS WeeksDiff

🔍 A roundup of other common date functions and formatting (for RedShift and PostgreSQL): 

  • DATE(created_at)

  • created_at >= CURRENT_DATE - INTERVAL '15 days'

  • created_at > GETDATE() - 60 (or created_at::DATE if date is not formatted)

  • DATE_TRUNC('month', created_at) (or DATE_TRUNC('month', created_at::DATE if date is not formatted)

  • MIN(created_at) / MAX(created_at)

  • EXTRACT(MINUTE FROM (item.created_at::TIMESTAMP - order.created_at::TIMESTAMP))

  • select LAST_DAY(created_at)

  • NEXT_DAY('2020-01-01', 'Friday')

  • DATE_PART_YEAR(created_at)

Let me know if you stuck with SQL or need help with formatting or special data extraction - I have a big love for SQL 😍

If you haven’t yet, make sure to subscribe to learn more about sql functions:

Share this post

SQL Date-Time Cheatsheet

dataanalysis.substack.com
Previous
Next
Comments
TopNewCommunity

No posts

Ready for more?

© 2023 Olga Berezovsky
Privacy ∙ Terms ∙ Collection notice
Start WritingGet the app
Substack is the home for great writing