I’m very new to SQL and time series database. I’m using crate database ( it think which is used PostgreSQL).i want to aggregate the data by hour,day ,week and month. Unix time stamp is used to store the data. following is my sample database. i tried the sql query using FROM_UNIXTIME not supported …
Tag: date-arithmetic
how can i check if value exists before the date specified in sql server
I have the data below in a sql table, let assume the user wants to get data of 2020-11-13 which is but i’d like to get the previous supplier due as well before the date specified which is along with so the actual query i wanna get is this and if there is no previous due i wanna return i
Writing a sub-query using a date clause
So basically I’m trying to write something that prints the customer’s name and area code if they have made a booking within the last 6 months of the current date. My code looks like this (it has to be a subquery, not using join) Yet it doesn’t work, but I can get it to work using a join func…
Order count per ID and calculate time between orders in BigQuery
I am working with customer purchase data and am trying to write a query in Google BigQuery that sorts all purchases by date and adds a purchase/order count per customer (order_count). Also, I would like to calculate a time-delay (in days) between the orders of one customer (purchase_latency). My query current…
Get the logs of the current week in postgresql
I need to get the logs of the current week in postgresql, I have tried to use the native mysql functions yearweek for that, but here in postgresql it is different This is my table CREATE TABLE …
Rolling Sum for Last 12 Months in SQL
I’m trying to get the rolling sum for the past 12 months (Oct 2019-Sept 2020, etc.)> So far, I figured out to get the current year total (which I also want), however, I’m stuck on a legit 12 month rolling sum. here’s what my current output looks like AS you can see, it resets at the begin…
How to pull data for past x weeks dynamically in redshift?
I have a below query which I run gives me the single count for previous week which is Week 43. Current week is 44 as of now. As of now the output I see is this which is for Week 43- Now I am trying to make this query dynamic such that it can give me count for past 6
How to unnest the table based on date interval in Presto?
I have a table with events, where start_dt is a start and end_dt an end of the event. This table is partitioned by dt column derived from the end_dt. This means that events that start before and end after midnight are present only in one partition. What I need to do is to split each event into as many rows
Generating counts of open tickets over time, given opened and closed dates
I have a set of data for some tickets, with datetime of when they were opened and closed (or NULL if they are still open). We would like to generate a table of data showing the total count of tickets that were open through time, grouped by date. Something like the following: Note that I am not sure about how
mysql with date function query running slow
I found something weird while executing query today and i want to know how this happens. Below is my query: this query takes 2-5 seconds while searching for the data. Now i did small change in the query as follow: In this case query takes 2-3 minutes. Here testing_date column data in dateTime format for examp…