How do I find the first day of previous/next week with SQL?

I saw your article the other day on getting the first date of the current week.. But what if I wanted to get the first date of the previous week? Or the first date of next week? As you've already guessed, the SQL syntax for getting the first day of the current week will require … Continue reading How do I find the first day of previous/next week with SQL?

How do I find the first day of the current week with SQL?

Have you ever had to do weekly/monthly reporting based on SQL data retrieval? It would be pretty neat if you could get the start date of the current week/month dynamically, no? Well, there's a very straightforward way if you're using Vertica or PostgreSQL - just employ the date_trunc('period', timestamp) function. Other databases (e.g. SQL Server,  MySQL) … Continue reading How do I find the first day of the current week with SQL?

How do I calculate the remaining days until the end of a calendar quarter?

It may sound overwhelming but it's actually quite simple, especially if you've already figured out how to: extract various time periods from a date get the end date of the current quarter calculate the number of days between two days As you'll see below - calculating the remaining days in current quarter is then nothing … Continue reading How do I calculate the remaining days until the end of a calendar quarter?

How do I find the difference between two dates with SQL?

Whether you're: calculating key supply chain indicators trying to forecast orders/sales/revenue until the end of quarter/year, you're very likely to find yourself in the need of calculating the difference between two dates (timestamps)... A way to go about it would be to use the datediff('years', start date, current date) function. The great thing about the datediff() … Continue reading How do I find the difference between two dates with SQL?

How can I get the end date of a quarter with SQL?

It will depend on the database you're using! As you can see below, there can be quite some variations in syntax between Vertica, SQL Server, MySQL, PostgreSQL   So, what's common in all these examples? In all cases, you will need: a function that gets the current date a function that extracts the year from … Continue reading How can I get the end date of a quarter with SQL?

How do I extract part of a date with SQL? (2)

Yesterday, we looked at the standard approach of aggregating daily data stretching over a longer time periods If you're lucky enough to be working with Vertica or MySQL, you can also rely on functions like: year(), quarter(), month(), week(), day() SQL Server also supports some of them - year(), month(), day() - but is by far the most limited of … Continue reading How do I extract part of a date with SQL? (2)

How do I convert a timestamp to another timezone with SQL?

Have you ever had that request? I would like to have the distribution of clicks per hour for the last week in local time It's a no brainer if your database server is in the same time zone as your business audience  - then you just need to aggreate the clicks' timestamps to an hour. Not … Continue reading How do I convert a timestamp to another timezone with SQL?