Tag: date-arithmetic

Aggregate data based on unix time stamp crate database

aggregate-functions date-arithmetic datetime postgresql sql

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 . please help me? im looking the

how can i check if value exists before the date specified in sql server

c# date-arithmetic lateral-join sql 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

date-arithmetic datetime mysql sql subquery

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 function, but

Order count per ID and calculate time between orders in BigQuery

count date-arithmetic datetime google-bigquery sql

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 currently looks like this: The result including “order_count” and “purchase_latency”

Get the logs of the current week in postgresql

date-arithmetic datetime postgresql sql where-clause

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

date-arithmetic lateral-join sql sql-server sum

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 beginning of the year

How to pull data for past x weeks dynamically in redshift?

amazon-redshift date-arithmetic datetime sql where-clause

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?

date-arithmetic gaps-and-islands presto recursive-query sql

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

count date-arithmetic left-join postgresql sql

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

date-arithmetic mysql query-optimization sql where-clause

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 example : 2020-06-01 00:00:00 Here