How to select records from the last 24 hours in PostgreSQL
Posted by Patrick Gibson
Observing your data from the last 24 hours is a great way to gain insight into your daily company performance. Whether you are tracking the sales of a new product or following user activity the day after a promotion, much can be said about this short period of time. In this tutorial, we will look at different ways to write a query to select records from the last 24 hours.
Selecting the last 24 hours
For example, let’s consider a table named “users” with records containing the name, address, and other information of a new user. More importantly, the table contains the attribute “created_date” which holds the time and date of when the user joined. The following shows how we can select the users who have joined in the past 24 hours in order of earliest to most recent:
SELECT *
FROM public.users AS "Users"
WHERE "Users"."created_date" BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()
ORDER BY "Users"."created_date" DESCThis is all done using a WHERE clause. We are selecting the records who have a created date that lies between the current time and 24 hours before the current time. A different query which accomplishes the same thing could look something like this:
SELECT *
FROM public.users AS "Users"
WHERE "Users"."created_date" >= NOW() - INTERVAL '24 HOURS'
ORDER BY "Users"."created_date" DESCIn this case, we are selecting the records who have a created date which is from 24 hours before the current time up to the current time. In either case, we obtain the same result. There are similar PostgreSQL functions that can help assist in this task, and you can refer here for more information on these Date/Time functions. We note that if we want to change the period of time to search, we would edit the WHERE clause to represent the desired time frame to search.
Relative date variables
Atlassian Analytics offers built-in date variables which can assist in keeping your data up to date with each refresh. These variables can be put to use throughout the application wherever you enter a date. A portion of the date variables include:
{TODAY}{CURRENT_ISO_WEEK.START}and{CURRENT_ISO_WEEK.END}{CURRENT_QUARTER.START}and{CURRENT_QUARTER.END}
Using these relative date variables will ensure that all of your dashboards in Atlassian Analytics will refresh properly. Functions on these date variables also allow you to customize the return date to fit your needs. For example, we could write the day before today (yesterday) as:
{TODAY.SUB(1,'day')}
We can expand on the queries in this tutorial to gain further insight depending on what we would like to analyze, but this gives us a good starting point to retrieve any information from the previous 24 hours.