Tag: datetime

recursively creating multiple rows for every date between two dates in one row

common-table-expression datetime postgresql recursive-query sql

I have staff holidays (rdos) stored in a single row, I am very close to recursively expanding them to show a full row for each day of leave. Running this on Postgres Database version 10.10 Data Set: My query currently: I am getting duplicate regressions and unwanted cross matches. Any ideas? I have seen similar solutions that rely on a

When selecting data with DATE_FORMAT() is not getting proper values MySQL

datetime mysql sql string where-clause

I’m having a table structure as below. insert statements : when i select with below query I’m not getting the proper out. Output : I need a data where dt values are less than 90 Days. Can anyone suggest where to make changes. Answer You are overcomplicating things here I think. Basically you are turning the dates to strings in

SQl Query to get an output that resembles the image [closed]

datetime gaps-and-islands sql sql-server sum

I would like to get output the way its shown in the image attached and the way the input is organized is also shown in the image attached. Thanks!

Postgres: how to check for records in time buckets for multiple values

aggregate-functions count datetime postgresql sql

I have a POSTGRES table with roughly the following shape: sensor_id | recorded_at ———-+——————– A | 2020-01-01 00:00:00 A | 2020-01-01 00:03:00 B | …

MySql select all records from the table with month and year in the range from that month and year to all the past [closed]

datetime mysql sql where-clause

I have a table with two fields “month” and “year” how can I make a query that, passing a month and a year, select everything from that month and year back. For example, if the …

Only include values from the latest timestamp into the query result

datetime mysql sql subquery sum

DB-Fiddle CREATE TABLE sales ( id int auto_increment primary key, time_stamp TIMESTAMP, product VARCHAR(255), sales_quantity INT ); INSERT INTO sales (time_stamp, product, …

How sum values in days intervals MySQL 5.7?

datetime mysql pivot sql sum

I have a server with MySQL 5.7. I have two tables. First one t contains creating dates for each id. Second table t0 contains profit records day by day for each id. I want to get columns with sums of profit for first and second 30 days for each id as well as for the first day. This code runs

Get yearly average of per id

average count datetime oracle sql

This code gets the number of ids that that show up for each month with in the year. This is the result i get: I want to get the yearly average of each ID, and the overall count. The results will look like this: Answer You can add another level of aggregation: Notes: you don’t actually need a CTE to

Find Gaps in a single date column SQL Server

date datetime sql sql-server tsql

Good Day everyone, I need your help. I am trying to detect gaps in a single column of the type Date or DateTime in SQL Server. Say we have a list of schools and each school has many records and there is a field of uploadDate. So something like that: My outcome would be something like that: Thank you all.

Incorrect datetime value in MySQL table

datetime mysql sql

I have just created a new MySQL in an AWS Ubuntu instance. Then I have copied a table from another MySQL server to the new created database. This is the structure from some fields from one of the tables: My issue is that every time I try to enter a new record on the table, there is an error at