Implementing a rotating partition scheme, per [kejser.org/table-pattern-rotating-log-ring-](https://web.archive.org/web/20220630220433/kejser.org/table-pattern-rotating-log-ring-buffer/) . Ran into an issue with DATEDIFF rounding up values: DECLARE @Partitions INT = 15; SELECT a1.dt , dtTrunc , dtDiff , PartitionKey = CAST(DATEDIFF(DAY, 0, dtDiff) % @Partitions AS TINYINT) FROM ( VALUES ('2024-08-17 23:59:59.997') , ('2024-08-17 23:59:59.998') , ('2024-08-17 23:59:59.999') , ('2024-08-18 00:00:00.000') ) AS v(dt) CROSS APPLY ( SELECT dt = CAST(v.dt AS DATETIME2(3)) ) a1 CROSS APPLY ( SELECT dtTrunc = CAST(a1.dt AS DATE) , dtDiff = DATEDIFF(day, 0, a1.dt) ) a2 Problem solved with cast to date: DECLARE @Partitions INT = 15; SELECT a1.dt , dtTrunc , dtDiff , PartitionKey = CAST(DATEDIFF(DAY, 0, dtDiff) % @Partitions AS TINYINT) FROM ( VALUES ('2024-08-17 23:59:59.997') , ('2024-08-17 23:59:59.998') , ('2024-08-17 23:59:59.999') , ('2024-08-18 00:00:00.000') ) AS v(dt) CROSS APPLY ( SELECT dt = CAST(v.dt AS DATETIME2(3)) ) a1 CROSS APPLY ( SELECT dtTrunc = CAST(a1.dt AS DATE) , dtDiff = DATEDIFF(day, 0, CAST(a1.dt AS DATE)) ) a2 Is this expected / documented behaviour? If so, where?

I'm trying to calculate days between two dates. Let's say I have the date **'2005-05-25 06:04:08'** and I want to count how many days are there between that day and the current date. I tried doing:

SELECT DATE_PART('day', AGE(CURRENT_TIMESTAMP, '2005-05-25 06:04:08'::timestamp )) AS days;

But for some reason this returns 11, it's not taking into account the difference in years. How can I solve this?

I am using 2 tables Members (MemberID, MemberName, Department, JoinDate, LeaveDate) Subscriptions (ID, MemberName, DatePaid, AmountPaid) I have combined them SELECT Members.MemberID, Members.MemberName, Members.Department, Members.JoinDate, Members.LeaveDate, Subscriptions.MemberName, Subscriptions.DatePaid FROM Subscriptions LEFT JOIN Members ON Subscriptions.MemberName = Members.MemberName I am trying to add a column to calculate the number of weeks - DateDiff(JoinDate,CurDate()) as Weeks and also Sum(Subscriptions.AmountPaid) Can do individual, but want in same table myphpadmin -Database client version: libmysql - mysqlnd 8.2.11PHP extension: mysqli Documentation curl Documentation mbstring Documentation sodium Documentation PHP version: 8.2.11 –

Starting from a table with dates in descending order, starting from the current_date:

date
----------
2023-02-03
2023-02-02
2023-02-01
2023-01-31
...

I want to add a column that contains a group number for the rolling month:

date        rolling_month
----------  -------------
2023-02-03  1
2023-02-02  1
2023-02-01  1
2023-01-31  1
... 
2023-01-04  1
2023-01-03  2
2023-01-02  2
...
2023-01-04  2
2023-01-03  2
2023-01-02  3
... 
2022-12-04  3
2022-12-03  3
...

Is this possible? What SQL to use? maybe a window function? I tried this, but keep getting trouble with the number of days that can be either 28, 29, 30 or 31. Any suggestions will be appreciated. --- Update Here's a similar query, that works for rolling weeks and rolling fortnights:

with dim_date as (
	select date::date  from generate_series(now()- interval '2 years', now() + interval '2 years', '1 day') as date
)
    SELECT
      date,
      row_number() OVER (ORDER BY date DESC) rev_order,
      floor((row_number() OVER (ORDER BY date DESC)-1) / 7) AS rolling_week, 
      floor((row_number() OVER (ORDER BY date DESC)-1) / 14) AS rolling_fortnight
    FROM
      dim_date
    WHERE
      date IS NOT NULL
      and date <= CURRENT_DATE
    ORDER BY date DESC;

and here's two of my (failing) attempts to crack it:

with dim_date as (
	select date::date  from generate_series(now()- interval '2 years', now() + interval '2 years', '1 day') as date
)
select 
	date,
	rank() over (
		order by date desc
		range between interval '1 month' preceding and current row)
from star.dim_date 
where
	date is not null
	and date < current_date
order by date desc;

with dim_date as (
	select date::date  from generate_series(now()- interval '2 years', now() + interval '2 years', '1 day') as date
)
select
	date,
	row_number() over (order by date desc) rev_order,
	(date - interval '1 month')::date one_month_ago, 
	date - (date - interval '1 month') as interval,
	extract (DAY from (date - (date - interval '1 month'))) as interval_days, 
	floor((row_number() over (order by date desc)-1) / extract (DAY from (date - (date - interval '1 month')))) as rolling_month
from
	dim_date
where
	date is not null
	and date < current_date
order by date desc LIMIT 1000;

I was thinking that I probably should subtract the duration of the previous month unless the day considered march 30 or if the day considered is the 31st; unless there is a way to use to subtract an interval '1 month' intelligently perjaps in combination with the range in the window function.

At the moment I've got an sqlite3 database that keeps track of the state of my smart home devices. The relevant parts of the schema for the main table are

CREATE TABLE states(
    state_id INTEGER NOT NULL,
    entity_id VARCHAR(255),
    state VARCHAR(255),
    last_updated DATETIME
);

I'm trying to calculate how long each entity has been in the state "on" for each day. Currently my thought would be to use the lead window function to create a column with the next updated time:

CREATE VIEW states_with_next_update AS
  SELECT *, lead(last_updated,1) over (PARTITION BY entity_id) as next_update
  FROM states;

and then it would be possible to subtract the next updated time from the current time to get the total time each entity was in a certain state for.

CREATE VIEW states_with_durations AS
  SELECT *, julianday(next_update) - julianday(last_updated) as state_duration, date(last_updated) as day
  FROM states_with_next_update;

With the duration of each state I can now use aggregate functions to calculate the total time each was in the "on" state:

SELECT day, entity_id, sum(state_duration)
FROM state_with_durations
WHERE state = "on"
GROUP BY day, entity_id;

The only problem with this method is that next_update may not fall in the same day leading to durations that contain time from two (or more) consecutive days, which leads to over counting on the first day, and under counting on the subsequent. For example if an entity is in the state "on" from 2022-11-10 20:00 to 2022-11-11 02:00, the total for 2022-11-10 would read 6, and the total for 2020-11-11 would read 0. So the question is, how do I make it so that in the example above the sum for the "on" state reads 4 hours for the day 2022-11-10, and 2 hours for 2022-11-11?

I have the following query so far and unfortunately, I cannot use *regexp* or *greater than* operators, I can only use the LIKE keyword. The whole column is in a json string, I can't use json_value or regexp because I'm on SQL Server so I'm stuck with using LIKE. It's SQL Server 2014 and json_value is not supported until 2016. SELECT * FROM DataTableOne WHERE update_date LIKE '%1645290000%' I would like to retrieve all records where the epoch unix timestamp is greater than 1645290000 using only the SQL LIKE keyword (or even between 1645290000 and 9999999999 using the SQL LIKE operator). Any help will be much appreciated since this is a very tough unique case where I am limited to using only the LIKE keyword. Sample table/data below:

CREATE TABLE DataTableOne (
    ID int,
    DATA varchar(MAX)
);

INSERT INTO DataTableOne (ID, DATA)
VALUES (1, '{"name":"Cole", "update_date":"2855290000"}'),
(2, '{"name":"Peter", "update_date":"1222290000"}') ;

There could be a thousand rows with this sort of data and the only ones I want are the ones where the update_date is greater than 1645290000. Running the query on the above table I gave should only return the first row since the *update_date* of 2855290000 is indeed greater than 1645290000 numerically.

I have a PostgreSQL table called tickets_details, which has many columns and is constantly updated only on the rows of the current day, it also inserts thousands of rows of the current day that have duplicate data in several columns, especially a column called td_date of type timestamp the which is used in all queries of this table to find rows for specific dates, especially the current day. Currently I have performance problems when making queries because in the td_date column of type timestamp I perform a cast to convert it to date format and when I try to create the following index to optimize at least the data of the current day I got the following error:

CREATE INDEX queries_recent_idx
                  ON sl01.tickets_details (td_date)
                  WHERE td_date::DATE = '2022-06-19';
ERROR:  functions in index predicate must be marked IMMUTABLE

What would be the correct way to create this index or any suggestions on how to partition this table, since it grows very fast due to the number of transactions and to insert each row I must make a sum of the sale of x product and validate several parameters of the rows already inserted on that day, I am open to suggestions that can help me to carry out the transactions efficiently by validating the data of the current day and the index for queries of reports, I would appreciate any help. When I make the queries I use (timezone('AST'::text, now()))::DATE. One of the many queries this table has is this:

SELECT 
COALESCE(SUM(t.td_amount), 0) AS sales, 
COALESCE(SUM(t.td_commission::FLOAT)::INT, 0) AS commission, 
COALESCE(SUM(p.prize), 0) AS prize,
COALESCE(SUM(t.td_amount), 0) - COALESCE(SUM(t.td_commission::FLOAT)::INT, 0) - COALESCE(SUM(p.prize), 0) AS sumary 
FROM sl01.tickets_details t
LEFT JOIN LATERAL (SELECT tx_id, numbers, draw_id, username, COALESCE(SUM(prize), 0) AS prize FROM sl01.prizes GROUP BY 1, 2, 3, 4) p ON p.tx_id  = t.tx_id AND p.numbers = t.number_played AND p.draw_id = t.draws_id AND p.username = t.user_id
WHERE t.td_date::DATE = timezone('AST', now())::DATE
AND t.td_status IN ('APROBADO', 'PAGADO', 'NO PAGADO')

There are actually more than 40 functions that use data from this table for updates, inserts and sum queries, they all have in common the date the rows were inserted, it currently has more than 3 million rows. td_date is of type timestamptz.

I have a SQL query that looks for values in a table on a range of dates. If no record is found, I would like to generate rows with default values. Example of one of the table existing records: DeviceId| Time | Context |Value --------|----------|---------|----- 1 |2022-02-10|Connected|False So a query that restricts on the Time column between 2022-02-07 and 2022-02-10 must create fake rows for February 7th, 8th, and 9th but not for the 10th because that already exists. Expected result: DeviceId| Time | Context |Value --------|----------|---------|----- 1 |2022-02-7 |Fake |False 1 |2022-02-8 |Fake |False 1 |2022-02-9 |Fake |False 1 |2022-02-10|Connected|False How can I do that? With a recursive CTE?

My question is similar to https://stackoverflow.com/a/41267928/2585154 , but in my case I need to divide/split date range by multiple date ranges, not only one. In my case is also guaranteed that "date ranges to split by" will not overlap with each other. **Example 1:** Date range to split: 2021-01-01 2021-12-31 Date ranges to split by (1): - 2021-03-01 2021-06-02 I want to get following output: - 2021-01-01 2021-02-28 - 2021-06-03 2021-12-31 **Example 2:** Date range to split: 2021-01-01 2021-12-31 Date ranges to split by (2): - 2021-03-01 2021-06-02 - 2021-07-01 2021-12-30 I want to get following output: - 2021-01-01 2021-02-28 - 2021-06-03 2021-06-30 - 2021-12-31 2021-12-31

I have seen on this site how to get the the third Friday of the month: select quote_date FROM table where datename(weekday, quote_date) = 'Friday' and datepart(day, quote_date)>=15 and datepart(day, quote_date)<=21; How would I get the first Monday after this third Friday?

I have the following table with the following data:

DECLARE @MyActions TABLE (ActionId INT NOT NULL, ActionDate DATETIME NOT NULL)

INSERT INTO @MyActions VALUES (1, '2021-08-01 01:00:00')
INSERT INTO @MyActions VALUES (2, '2021-08-02 02:00:00')
INSERT INTO @MyActions VALUES (3, '2021-08-03 03:00:00')
INSERT INTO @MyActions VALUES (4, '2021-08-04 04:00:00')

How do I explode out each ActionID into 10 additional records (5 days before and 5 days after the date for current ActionID) without using slow table value function? ActionID = 1 should explode out to 11 total records (without the time), Action 2 should explode out to 11 total records (without the time), etc Exploded Resultset

-none
1	2021-07-27 00:00:00.000
1	2021-07-28 00:00:00.000
1	2021-07-29 00:00:00.000
1	2021-07-30 00:00:00.000
1	2021-07-31 00:00:00.000
1	2021-08-01 00:00:00.000
1	2021-08-02 00:00:00.000
1	2021-08-03 00:00:00.000
1	2021-08-04 00:00:00.000
1	2021-08-05 00:00:00.000
1	2021-08-06 00:00:00.000
2	2021-07-28 00:00:00.000
2	2021-07-29 00:00:00.000
2	2021-07-30 00:00:00.000
2	2021-07-31 00:00:00.000
2	2021-08-01 00:00:00.000
2	2021-08-02 00:00:00.000
2	2021-08-03 00:00:00.000
2	2021-08-04 00:00:00.000
2	2021-08-05 00:00:00.000
2	2021-08-06 00:00:00.000
2	2021-08-07 00:00:00.000
3	2021-07-29 00:00:00.000
3	2021-07-30 00:00:00.000
3	2021-07-31 00:00:00.000
3	2021-08-01 00:00:00.000
3	2021-08-02 00:00:00.000
3	2021-08-03 00:00:00.000
3	2021-08-04 00:00:00.000
3	2021-08-05 00:00:00.000
3	2021-08-06 00:00:00.000
3	2021-08-07 00:00:00.000
3	2021-08-08 00:00:00.000
4	2021-07-30 00:00:00.000
4	2021-07-31 00:00:00.000
4	2021-08-01 00:00:00.000
4	2021-08-02 00:00:00.000
4	2021-08-03 00:00:00.000
4	2021-08-04 00:00:00.000
4	2021-08-05 00:00:00.000
4	2021-08-06 00:00:00.000
4	2021-08-07 00:00:00.000
4	2021-08-08 00:00:00.000
4	2021-08-09 00:00:00.000

Is there a T-SQL coding best practice to get an accurate standard deviation value for a group of times? STDEV doesn't like the time data type. I'm thinking that maybe a conversion to minutes as an integer, but a conversion from/to what? Any suggestions?

I have a table as below:

Name  Start Date  End Date
Joe   20/04/2021  20/05/2021
John  01/05/2021  28/05/2021

I am using a SQL table-valued function to return a table that has 2 columns: Month and Count total Date of this month. Example: - Joe: 10 days in Apr, 20 days in May - John: 28 days in May Finally I will return a new table Month|Count -|- 4|10 5|48 I tried to use datediff and datepart to group by month, but don't know how to sum after group. Is there any way to do this? Besides, I want to add filter from date and to date.

How to store office hours in PostgreSQL rows, so that I can calculate the office hours. Example: * We have open from 9:00 to 18:00 from Monday till Friday. * Saturday we have open from 10:00 to 15:00 * from 24. Dec to 31. Dec we have open from 10:00 to 13:00 (but not on Saturday and Sunday) * bank holidays like 25/26 December are closed. * All above rules are valid until 31. Dec 2021. * Starting from 01. January 2022 we have open from 10:00 to 18:00 from Monday till Friday. Saturday is open like before. I would like to store this data in rows, so that we can develop an interface for it. And then there needs to be a method which uses the rows/rules to calculate the specific opening hours. Different timezone are not important in this context. I use PostgreSQL version 12.6. But if needed I can upgrade to a newer version.

I need to get a count of rows in a table day wise. The table looks like below: Table ABC: f_id|reg_date 1|2020-09-08 2|2020-09-12 3|2020-10-01 4|2020-09-07 5|2020-09-08 6|2020-09-09 Expected output if I am running query saying I want the count of rows till 2020-09-15 since a week before of 15th: count|date 1|2020-09-07 3|2020-09-08 4|2020-09-09 4|2020-09-10 4|2020-09-11 5|2020-09-12 5|2020-09-13 5|2020-09-14 I am not sure how to get this above output. The rouble with the date range and group by is just giving me the the count of rows for that date, not a total of the count till that date. For example group by date gives me: 1|2020-09-07, 2|2020-09-08 and so on.

I am using SQL Server 2016 I have a table that contains 1 row per month that a patient is assigned to a particular Provider. A patient can be assigned to multiple providers during the year. How can I derive date spans (startdate & enddate) to represent the time a patient was assigned to each provider. My table looks like this: +----------+---------------+------------+-----------+ | Provider | Patient | StartDate | EndDate | +----------+---------------+------------+-----------+ | 1922157 | 12345 | 20191201 | 20191231 | | 1904176 | 12345 | 20191101 | 20191201 | | 1904176 | 12345 | 20191001 | 20191101 | | 1904176 | 12345 | 20190901 | 20191001 | | 1904176 | 12345 | 20190801 | 20190901 | | 1904176 | 12345 | 20190701 | 20190801 | | 1904176 | 12345 | 20190601 | 20190701 | | 1904176 | 12345 | 20190501 | 20190601 | | 1904176 | 12345 | 20190401 | 20190501 | | 1904176 | 12345 | 20190301 | 20190401 | | 1904176 | 12345 | 20190201 | 20190301 | | 1922157 | 12345 | 20190101 | 20190201 | | 1922157 | 56789 | 20190101 | 20190201 | +----------+---------------+------------+-----------+ In this case, patient 12345 was assigned to 2 different providers. One for 2 months, January and then December and the other for the rest of the year (10 months) February through November. Patient 56789 was only assigned to 1 provider (1922157) for 1 month (in December). I'm trying to make it so my output looks like the below table but I am running into issues I think because the patient is assigned to the same pcp during 2 different times of the year. I tried using the lag function but I only get the correct results for some cases but not all such as this particular case. +----------+---------------+------------+-----------+ | Provider | Patient | StartDate | EndDate | +----------+---------------+------------+-----------+ | 1922157 | 12345 | 20190101 | 20190201 | | 1904176 | 12345 | 20190201 | 20191201 | | 1922157 | 12345 | 20191201 | 20191231 | | 1922157 | 56789 | 20191201 | 20191231 | +----------+---------------+------------+-----------+ Update: Was doing some more research and came across the following post: https://stackoverflow.com/questions/35900765/ms-sql-combine-date-rows-into-start-end-date I just fit my table into the code in the answer for above question and tested for a few of my cases and it looks like it might get the job done. Unfortunately, my base table has 140k rows of dates it will need to calculate through so I am not sure how long it will take to run. Has been running now for 6 minutes, I will post back with results.

There is a timestamp column I use to indicate whether a row was created during day or night time. My code is the following, but for some reason I only get 'DAY' as outcome. Am I not formatting the values right?

select record_id, rec_date,
       case when date_part('hour', rec_date) between 20 and 07 then 'Night'
            else 'Day' end as Indicator
from records;

The rec_date column is a timestamp where I can see values such as 2019-11-20 21:34:02.000000 - which should get a 'Night' indicator.

I am running this all the time to get HH:MM:SS from a number of seconds: DECLARE @s INT SELECT @s = 23251 SELECT @s , CONVERT(TIME, DATEADD(SECOND, @s, 0)); when I want to add days to the equation I run the following query: DECLARE @seconds AS int = 232511; SELECT CONVERT(varchar, (@seconds / 86400)) --Days + ':' + CONVERT(varchar, DATEADD(ss, @seconds, 0), 108); --Hours, Minutes, Seconds what if I wanted as well the number of weeks, months, and years? How would I go around that? I think the exact calculation would need to have a reference to when the number of seconds happened. Let's say for instance I am interested in how long a sql server job has been running for. I have jobs within the replication category that have been running for years, and the starting point is known. When not specified we could assume the number of seconds are up to now.

I am puzzled by the following behaviour in SQL Server (tested on SQL Server 2017). The goal is to return the number of seconds between two different datetime values. The two datetimes typically lie pretty close to another (differences measured between 0.4 and 2 seconds). I started with the simplistic expression DATEDIFF(SECOND, startDateTime, endDateTime) and assumed that it might return 0 seconds for up to 499 (in fact due to precision 497) milliseconds and round up to 1 second for values starting at 500 milliseconds. I read the MSDN Documentation but didn't get any wiser. It mentions something about "datepart boundaries" stating that "Those dates are adjacent and they differ in time by a hundred nanoseconds (.0000001 second). " I have milliseconds in difference. Does that also qualify as "being adjacent"? However this is not how it works. It seams that if a boundary to the next minute is crossed the value of DATEDIFF(SECONDS...) always yields 1 while for the same interval all within one minute the seconds yield 0. Please have a look at rows with id 8 and 9 as well as 10 and 11 in the following result set: Here's the code for the example: WITH cteValues AS ( SELECT id, CAST(startDateTime AS DATETIME) AS startDateTime, CAST(endDateTime AS DATETIME) AS endDateTime FROM ( VALUES ( 1,'20200202 23:56:19.737', '20200202 23:56:21.723'), ( 2,'20200202 23:56:25.690', '20200202 23:56:27.297'), ( 3,'20200202 23:56:31.453', '20200202 23:56:32.753'), ( 4,'20200202 23:56:36.940', '20200202 23:56:37.533'), ( 5,'20200202 23:56:37.353', '20200202 23:56:37.927'), ( 6,'20200202 23:56:38.653', '20200202 23:56:39.213'), ( 7,'20200202 23:56:42.630', '20200202 23:56:43.163'), ( 10,'20200203 06:34:50.900', '20200203 06:34:51.363'), ( 8,'20200203 06:34:50.100', '20200203 06:34:50.600'), ( 9,'20200203 06:34:50.800', '20200203 06:34:51.300'), ( 11,'20200203 06:34:50.100', '20200203 06:34:50.563') ) t (id,startDateTime, endDateTime) ) SELECT id, startDateTime, endDateTime, CAST(endDateTime-startDateTime AS TIME) AS resultCast, DATEDIFF(SECOND, startDateTime, endDateTime) AS resultDatediffSeconds, ROUND(DATEDIFF(MILLISECOND, startDateTime, endDateTime) / 1000.0, 0) AS resultSecondsExtractedFromMilliseconds, DATEDIFF(MILLISECOND, startDateTime, endDateTime) AS resultDatediffMilliseconds FROM cteValues ORDER BY resultDatediffMilliseconds DESC Play for yourself in this SQL Fiddle . Why is that so? It is astonishing that the same command with the MILLISECOND option returns a precise amount of seconds but with the SECOND option it does not. Which way would you recommend to write the second calculation if one cares about accurately rounded results and is open for the best performing solution?

I'm writing a query for sales targets per week, however I'm getting a little stuck on this. Targets are per month, per year, so for example, Brand A has a monthly target (changes each month) The snippet from the report is as follows:

SELECT BrandName
      ,SUM((TargetMonThur *4) +TargetFriday)
WHERE [MONTH] IN (
        DATEPART(MM,@DAY1),
        DATEPART(MM,@DAY2),
        DATEPART(MM,@DAY3),
        DATEPART(MM,@DAY4),
        DATEPART(MM,@DAY5)
    ) 
    AND [YEAR] IN (
        DATEPART(YY,@DAY1),
        DATEPART(YY,@DAY2),
        DATEPART(YY,@DAY3),
        DATEPART(YY,@DAY4),
        DATEPART(YY,@DAY5)
    )

However the issue appears when the start of the week/ end of the week is a different month to the other days. at this point, it calculates both month's targets * 4. I think I'm being a complete noob on this, but any help would be great. Thanks in advance