A Brief Intro
Let’s say we have a created_at column in our database with data type big
integer that has a value with this equation:
EXTRACT(EPOCH FROM NOW()) * 1000
Don’t ask me why the value of
created_atlike that because i have no idea.
Now, the goals is to count every data per week.
How?
How to do that? Because we use a unix timestamp which look like this
1687746597339, we need to convert those into timestamp using postgresql
to_timestamp() function like this:
to_timestamp(created_at/1000)
We divide by 1000 because we multiple it by 1000 when we insert the value.
And then, we need to truncate into a specific date. We can use date_trunc()
function to truncate the date.
According to the documentation, this is the parameter for date_trunc():
date_trunc(field, source [, time_zone ])
Valid values for field are:
- microseconds
- milliseconds
- second
- minute
- hour
- day
- week
- month
- quarter
- year
- decade
- century
- millennium
source is either timestamp or interval and return timestamp or interval
depending on the source.
time_zone is an optional parameter to specify a different timezone. By
default, truncation is done with the current timezone setting.
If we combine date_trunc() and to_timestamp(), we will get something like
this:
date_trunc('week', to_timestamp(created_at/1000))
If we make the entire query, it would be something like this (let’s say the
table name is participants):
SELECT
date_trunc('week', to_timestamp(created_at/1000)) AS weekly,
COUNT(*) AS total_participants
GROUP BY weekly
ORDER BY weekly ASC;
And the result would be something like this:
weekly | total_participants
------------------------+--------------------------
2023-06-19 00:00:00+07 | 69420
2023-06-26 00:00:00+07 | 69
00:00:00+07is because the timezone setting on my database isasia/jakartawhich is basically UTC+7. If your timezone setting is different, the timestamp might be different.Still not sure why the
date_trunc()with fieldweekstart at monday, and, at the time of writing this post, i haven’t found a settings to change that.
Alright, that’s it. See you next time!