Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [date-math]

Use for queries that calculate dates or date ranges

This tag should primarly be used for queries where you need to define a range, flatten datetime to 0 hours, find the last day of a month or year, etc. It can also be used for queries that calculate quarters, weeks, financial timelines, holidays, etc.

35 questions
11
votes
3 answers

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…
anon
9
votes
2 answers

DATEDIFF Rounding

Implementing a rotating partition scheme, per kejser.org/table-pattern-rotating-log-ring-. Ran into an issue with DATEDIFF rounding up values: DECLARE @Partitions INT = 15; SELECT a1.dt , dtTrunc , dtDiff , PartitionKey =…
Mark Storey-Smith
  • 31,860
  • 9
  • 90
  • 125
7
votes
1 answer

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 parts work. For example, this will find dates from…
Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
5
votes
2 answers

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 regexp because I'm on SQL Server so I'm stuck with…
Elite298
  • 61
  • 6
5
votes
1 answer

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 integer, but a conversion from/to what? Any…
AccidentalDBA_CO
  • 157
  • 1
  • 7
5
votes
3 answers

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 desired interval is INTERVAL '10 minute', then…
Gajus
  • 1,334
  • 16
  • 29
5
votes
4 answers

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 road_condition (cond_id,road_id,cond_date,condition) values…
User1974
  • 1,517
  • 25
  • 54
3
votes
1 answer

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 duplicate data in several columns, especially a…
Cristofer Feliz
  • 33
  • 1
  • 1
  • 4
3
votes
2 answers

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 Output. Here is the Attached Screenshot of Sample…
ChandraSaiKarthik
  • 45
  • 1
  • 7
3
votes
2 answers

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 20181231 $10 20190131 $80 …
Daylon Hunt
  • 189
  • 1
  • 4
  • 16
2
votes
2 answers

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 @MyActions VALUES (2, '2021-08-02…
user2368632
  • 1,133
  • 1
  • 15
  • 33
2
votes
1 answer

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 from 24. Dec to 31. Dec we have open from 10:00 to…
guettli
  • 1,591
  • 5
  • 25
  • 51
2
votes
2 answers

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 values right? select record_id, rec_date, …
ColRow
  • 43
  • 7
2
votes
3 answers

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 CONDITION ------- ------- -------- --------- …
User1974
  • 1,517
  • 25
  • 54
1
vote
2 answers

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 NULL, entity_id VARCHAR(255), state…
CopOnTheRun
  • 113
  • 6
1
2 3