Questions tagged [date-math]

Use for queries that calculate dates or date ranges

Filter by
Sorted by
Tagged with
-2 votes
1 answer
41 views

Using sum and datediff from 2 tables [duplicate]

I am using 2 tables Members (MemberID, MemberName, Department, JoinDate, LeaveDate) Subscriptions (ID, MemberName, DatePaid, AmountPaid) I have combined them SELECT Members.MemberID, Members....
Mark V's user avatar
  • 9
0 votes
1 answer
210 views

how to convert a timestamp value stored in varchar column to proper format and increase/decrease to value of year

In a PostgreSQL table, I have a column named insert_date of type varchar(114) with values stored as below: 2023-04-08 01:42:12.197779000 +01:00 Now I would like to add or subtract a number of years ...
Hnarang's user avatar
0 votes
0 answers
62 views

Create a group number for rolling month

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 ...
Bart Jonk's user avatar
  • 305
1 vote
2 answers
274 views

Calculating the total time a device is on per day

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 ...
CopOnTheRun's user avatar
5 votes
2 answers
1k views

Records greater than epoch timestamp using only LIKE operator

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 ...
Elite298's user avatar
2 votes
1 answer
10k views

Recommended way to index a timestamp column in postgres?

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 ...
Cristofer Feliz's user avatar
11 votes
3 answers
3k views

Add default rows to the query result if restrictions are not met

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: ...
user avatar
1 vote
1 answer
1k views

Postgres find gaps between one date range and others

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 &...
Dmitry K.'s user avatar
  • 143
1 vote
2 answers
1k views

Getting the first Monday after the third Friday

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(...
Ivan's user avatar
  • 127
2 votes
2 answers
2k views

Explode a date range

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 ...
user2368632's user avatar
  • 1,097
5 votes
1 answer
717 views

Standard Deviation for Times

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 ...
AccidentalDBA_CO's user avatar
1 vote
1 answer
5k views

Calculate days between to dates postgresql

I'm having a problem: 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 from that day to the current date. I tried ...
Santi's user avatar
  • 11
0 votes
3 answers
12k views

Sum date between two date and group by month

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 ...
hem6d's user avatar
  • 15
2 votes
1 answer
930 views

Office Hours in PostgreSQL

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 ...
guettli's user avatar
  • 1,521
0 votes
1 answer
542 views

count of rows in table day wise

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 ...
ssssss's user avatar
  • 3
1 vote
1 answer
767 views

Derive Date Spans from Start and End Dates in SQL Server table

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 ...
Juan Velez's user avatar
  • 3,253
2 votes
2 answers
2k views

How to add a day/night indicator to a timestamp column?

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 ...
ColRow's user avatar
  • 43
0 votes
2 answers
1k views

Datediff SECOND with DATETIME type and rounding returns questionable results

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 ...
Martin Guth's user avatar
0 votes
1 answer
45 views

SQL Query- field calculation based on date

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 ...
Daniel Harris's user avatar
-1 votes
1 answer
940 views

Query to get yesterday date with specific amount of time

I am struggling to query for a specific time frame between 21:50 and 21:59: SELECT Username, EventTime, EventsEx.FirstName, EventsEx.Surname, PeripheralName, EventTypeDescription, CardNumber ...
Dzwono's user avatar
  • 1
0 votes
1 answer
683 views

Sum of values each hour at half past (that hour)

I need to aggregate the "sum" of values of a table with TS, Value columns of each half past hour (of 60 minutes), i.e. 00:30, 01:30, 02:30, etc Sample data: Val TS 1 2019-08-12 00:00:00.013 3 ...
edo's user avatar
  • 25
0 votes
0 answers
167 views

Nested Sum if statement with DateDiff

Problem: Need to account for delays in review process. The field "EC2WL" is when an employee submitted a case to the Work Leader for review and the field "WL1stReview" is when the Work Leader first ...
Jasuan's user avatar
  • 1
-1 votes
1 answer
128 views

DATE_SUB with configurable value

In MySQL you can use the DATE_SUB function to get a new date based on the values passed in. For example I can use the following to get the a date of 30 days ago from when I run the function SELECT ...
Joe W's user avatar
  • 1,039
3 votes
2 answers
191 views

Merge Only Events that Start immediately after the Previous Event without any Gap

How can i merge only the dates occur subsequently.? (i.e, The Next Event begins immediately after the previous event completed). Here, we need to consider some other Columns also for getting the ...
ChandraSaiKarthik's user avatar
3 votes
2 answers
1k views

Filter Table by a Historical 'Last Day of the Month'

I have a daily appending table, that I need a formula for that would filter the table results to just the last day of any given historical month. For example: Table: DwDate Balance ...
Daylon Hunt's user avatar
0 votes
1 answer
208 views

Converting MySQL 5.5 datetime binary to MySQL 5.6 (math problem)

I received a database "backup" of binary files (.frm and .ibd) that were copied from a MySQL 5.5 install. Unfortunately, I was unable to get these files to import into a MySQL 5.5 vm that I created. ...
Phillip's user avatar
0 votes
2 answers
786 views

How can I use mysql to determine the Sundays for the last year?

I have a table where I'm currently aggregating ~200 rows per day of stats. These stats include things like # of users, how many people using X feature, etc. What I'd like to do is query this table ...
Ben's user avatar
  • 215
0 votes
2 answers
115 views

Find Events between Dates

I have table with following data id start_date end_date 1 2018-01-01 2018-01-15 2 2018-01-11 2018-01-16 3 2018-01-03 2018-02-10 4 2018-01-01 2018-03-15 I want to list events based on ...
Muhammad Amjad's user avatar
-1 votes
1 answer
3k views

How do I exclude weekends (SQL) [closed]

I have different Year, Month and Day columns, And I wish to exclude weekends. How can I do so? Thank you very much!
BitTheBit's user avatar
1 vote
1 answer
2k views

Split datetime records that fall between 10pm to 7am into new record [closed]

I am very new to SQL Server. I am trying to split time in time out records that fall between night shifts from a employee shifts table but I am having a difficult time doing so. Night Shift runs from ...
Zeena's user avatar
  • 11
5 votes
3 answers
7k views

How do I round time to the upper multiple of an arbitrary time interval?

Example: If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '5 minute', then the desired output is 2018-05-17 22:50:00. If the current time is 2018-05-17 22:45:30 and the ...
Gajus's user avatar
  • 1,185
2 votes
3 answers
3k views

Multiply and divide dates

I had a recent DBA.SE question that involved dates that were used as the x-axis in a graph: Interpolate dates along a line. It was centred around a road_condition table: COND_ID ROAD_ID COND_DATE ...
User1974's user avatar
  • 1,457
5 votes
4 answers
920 views

Interpolate dates along a line

I have a road_condition table in Oracle 12c: create table road_condition ( cond_id number(5,0), road_id number(5,0), cond_date date, condition number(5,0) ); insert into ...
User1974's user avatar
  • 1,457
7 votes
1 answer
4k views

How does date math work in SQL Server?

I often see queries written with DATEADD and DATEDIFF in the WHERE clause to define a range, flatten datetime to 0 hours, or find the last day of a month or year, but I don't understand how all the ...
Erik Darling's user avatar
  • 40.8k
1 vote
2 answers
2k views

number of seconds to years-months-weeks-days-HH:MM-SS

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 ...
Marcello Miorelli's user avatar