Questions tagged [date-math]
Use for queries that calculate dates or date ranges
34
questions
0
votes
1
answer
69
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 ...
- 1
0
votes
0
answers
57
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 ...
- 293
1
vote
2
answers
182
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 ...
- 113
5
votes
2
answers
956
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 ...
- 61
1
vote
1
answer
6k
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 ...
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:
...
anon
1
vote
1
answer
872
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 &...
- 133
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(...
- 127
2
votes
2
answers
1k
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 ...
- 1,077
5
votes
1
answer
627
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 ...
- 137
1
vote
1
answer
3k
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 ...
- 11
0
votes
3
answers
10k
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 ...
- 15
2
votes
1
answer
782
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
...
- 1,421
0
votes
1
answer
411
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 ...
- 3
1
vote
1
answer
541
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 ...
- 3,235
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 ...
- 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 ...
- 665
0
votes
1
answer
39
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 ...
-1
votes
1
answer
848
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
...
- 1
0
votes
1
answer
614
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 ...
- 25
0
votes
0
answers
158
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 ...
- 1
-1
votes
1
answer
78
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 ...
- 1,009
3
votes
2
answers
186
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 ...
3
votes
2
answers
925
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
...
- 189
0
votes
1
answer
187
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. ...
- 5
0
votes
2
answers
627
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 ...
- 215
0
votes
2
answers
111
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 ...
- 103
-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!
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 ...
- 11
5
votes
3
answers
6k
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 ...
- 1,145
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 ...
- 1,437
5
votes
4
answers
905
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 ...
- 1,437
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 ...
- 38.2k
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 ...
- 15.8k