My data is too large to analyze since it is collecting every second or so. To reduce the data, I will like to group into interval of 5 minutes. I tried converting into unix timestamp and reverting it back, but didn’t work. I tried something like this Original data or output Desired output Answer I don&#…
Tag: intervals
how to check if date is within the same month as date in another column SQL BQ
I have two date fields in my BQ table. One is start_date and the other one is end_date. DataType for both columns is: DATE. Data looks like this: I need to create a new column and check if both dates are within the same month. The values would be “yes” and “no”. Yes if two dates are wi…
MySql using interval with a list of dates
I’m willing to fetch all the dates that are listed in the list reducing 1 day I’m trying something like that: I wish to receive: ‘2022-03-21’, ‘2022-03-17’, ‘2022-03-10’ Answer It can be done with intervals. Here is a link to how it works : https://www.db-fiddle…
How do I add number of days from original dates in new column
Hopefully a quick one on BigQuery I’ve tried intervals and days but can’t quite seem to get what I want. For date row on the example table below I want and adjacent row in a new column that just adds 42 days to the original date and time (time is always 00:00:00 if that helps). Desired output belo…
How to select whether or not a specific date is before X amount of years? – Presto SQL
I’m trying to select whether or not the date value in one of my columns takes place before a certain amount of years. The code I’m trying out: My expected output should be NULL for the dates I’m using (e.g. 2019-09-30) as they fall within the last 10 years, but for some reason, I’m get…
How to compute duration between two times in postgresql when the end time is sometimes past midnight
I have to compute the duration of events in postgres based on start_time and end_time, both are in the HH:MM:SS format. Simply end_time – start_time works: create table test_date as select sum(…
Calculate interval between boolean column change
I have a table with measurements of weather here is a simplified version of it: and I need to calculate how much time is been sunny. I know I need to use window functions but I’m stuck. Because I need this interval to be calculated in some range for example for last day. I have been able to make a
More than 24 hours in a day in postgreSQL
Assuming I have this schema: create table rental ( id integer, rental_date timestamp, customer_id smallint, return_date timestamp, ); Running this query returns strange …
SQL select the min and max values of the intersecting interval
In MsSQL 2014 I`ve got a table containing intervals of values Col1 Col2 1 3 2 4 7 12 7 15 14 21 I need to get the maximum and minimum values of the intersecting …
SQL: Split time interval into intersects of one hour
I am new to SQL, where I am trying to split time intervals into multiple rows of one hour, together with an indicator of how many minutes the interval overlaps each hour. My data: What I would like: What would be the easiest/fastest way to do this? Answer Try something like this: demo Result for sample data