Menu
I have a table with timestamp field and usage field like this
timestamp | usage
'2015-06-13 13:45:58' | 240
'2015-06-13 13:45:59' | 480
'2015-06-13 13:46:00' | 240
'2015-06-13 13:46:01' | 320
...
I want to get sum of usage for a period of '1 week' with '30 minutes' interval. I can get data only for intervals in minute, hour, day and ...
SELECT date_trunc('minute', timestamp) as clock, sum(usage)
FROM my_table
WHERE timestamp > localtimestamp - INTERVAL '1 week'
GROUP BY clock
how to get data for intervals like '5 minutes', '30 minutes', '2 days' and ... .
719
In PostgreSQL, the make_interval() function creates an interval from years, months, weeks, days, hours, minutes and seconds fields. You provide the years, months, weeks, days, hours, minutes and/or seconds fields, and it will return an interval in the interval data type.
In PostgreSQL the interval data type is used to store and manipulate a time period. It holds 16 bytes of space and ranging from -178, 000, 000 years to 178, 000, 000 years. It also has additional attribute called “precision (denoted by p)” that can be used to set the level of precision in the query results.
Select: Select is used to select timestamp value in timestamp syntax. We can select any date or time value to display the timestamp format in PostgreSQL. We can select the current date as now and the current timestamp function in PostgreSQL.
Get rows from past 24 hours in PostgreSQL In the above SQL query, we use PostgreSQL system function now() to get current datetime. Then we use INTERVAL clause to select those rows where order_date falls within past 24 hours of present datetime. You can also specify time interval in days, instead of hours.
Use the following to get what your after.
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + INTERVAL '1 second' * round((extract('epoch' FROM timestamp) / 1800) * 1800) AS clock, SUM(usage)
FROM my_table
WHERE timestamp > localtimestamp - INTERVAL '1 week'
GROUP BY round(extract('epoch' FROM timestamp) / 1800)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
