# Questions tagged [date-math]

Use for queries that calculate dates or date ranges

24 questions
Filter by
Sorted by
Tagged with
27 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 ...
75 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 ...
46 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 ...
60 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 ...
44 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 ...
762 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 ...
420 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 ...
36 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 ...
275 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 ...
85 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 ...
64 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 ...
21 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 ...
173 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 ...
172 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 ...
86 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. ...
142 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 ...
32 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 ...
1k 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!
1k 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 ...
3k 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 ...
2k 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 ...
763 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 ...