Questions tagged [date]
Questions related to date or date-related functions, data-types, representations (how dates might be stored and displayed) and operations (such as subtracting dates, adding intervals to dates).
346
questions
0
votes
0
answers
42
views
Complex day-to-day difference of historical data in SQL server
Setting:
I have a table (let's call it "HistoricalData") with 10 attribute columns, a date (in string format) and a value. The table has a unique clustered index on (date, idcol1, idcol2,.....
-1
votes
0
answers
30
views
Aggregate values with overlapping and partially overlapping date intervals
Hi I have a similar set of data to the question that was posted here however I also have a situation where there are partial overlaps in my data.
If I use the solution posted to that question it ...
0
votes
1
answer
19
views
I am getting an infinite fetch duration when adding a similar third left join
I am using MySQL on Windows 10.
The query below should result in three groups of data sets that are different only in the date range. It works for any two date ranges but when I try to run all three ...
- 1
3
votes
5
answers
1k
views
Optimizing SQL Server Query When Joining Tables on DATETIME column
I am working with SQL Server and I have two tables, table1 and table2. Both tables have a DATETIME column denoted as dt. I need to join these tables based not only on certain conditions but also to ...
- 1,157
1
vote
2
answers
44
views
TSQL count 2 different columns by using number table
I am trying to count 2 different date columns on a monthly interval by using a number table:
numbers table:
num
0
1
2
3
Originating Table (Example):
Gender
Subscription_Date
Cancel_Date
male
...
- 13
0
votes
1
answer
50
views
How to join matching Date fields as intervals?
Let say I've a table with a list of id/year/month (two years + 24 months for semplicity here):
Code Year Month
MAT000000001 2022 1
MAT000000001 2022 2
MAT000000001 2022 ...
- 243
-1
votes
1
answer
28
views
sql - how do I pick up all services that are open and latest of those ended - complicated
I am having issue capturing all rows that I need. A client can have number of services including same service again if that is an additional help, all services ended will have an end date and all open ...
- 1
0
votes
0
answers
20
views
How I can display the thumeric value of the day of week into Greek?
I have the following database:
create workers (
id SERIAL PRIMARY KEY,
name STRING
);
create works_zones (
id SERIAL PRIMARY KEY,
area_name String,
work_from TIME WITHOUT TIMEZONE,
...
0
votes
2
answers
112
views
SQL Count Value if the data is lower then current date
I want to verify the compliance by month. I already have the below query.
Only add the moment I am getting the following information
Only I would like to retrieve the following information add the ...
-2
votes
1
answer
126
views
Daily average from a 15mins interval datetime column o
I want to convert this 15 minutes interval data into daily average such that in the output file, I should have only two columns 'Date' and 'Daily_Avg_Value'.
This is my first post here - really wish ...
- 9
-2
votes
1
answer
45
views
How to calculate the age of a person in a string field with multiple date formats? [closed]
Bumped into a post of 9gag where someone made some fun of someone's coding (or just a joke of a coder). My question is more of a serious note, hypothetical speaking under set conditions, could that be ...
0
votes
0
answers
56
views
How to show all hours even no data in Firebird?
I want to write a query that shows all hours (for getting total order) between start date and end date even no data.
Result should be like this:
count
extract(hour)
date
0
1
2020-12-03
10
2
2020-12-...
- 1
0
votes
1
answer
30
views
how toget ordinal number of a day from beginning of year in postgresql?
I have a table like
id
created_at
1
2022-03-01
2
2022-03-23
The result should be
id
days_from_the_beginning_of_year
1
60
2
82
I can create a table like
date
...
- 103
2
votes
1
answer
576
views
How do I get the date next week in SQL (Firebird)?
I want to get a date using an SQL statement. For instance, if the date is '2018-08-07' output this should return: '2018-08-14' (one week later). Then I'm going to get the total rows between these ...
- 35
0
votes
0
answers
62
views
Overlap on Max/Min dates
I have data in a table that has gotten cut up with multiple start and end dates. This is due to an error that has happened a few times in the last year. Apparently causing the system to not see the ...
- 11
-2
votes
2
answers
137
views
Making SQL give age brackets on command
I'm trying to make SQL return how many users are inside each age bracket grabbing the date of birth from the user from the table GUEST and converting it into years, at the moment I'm trying to make it ...
0
votes
0
answers
92
views
MySQL select until 3 days of rows are met
I am trying to select dates including today through + 3 days but when nothing exists for a day, continue until 3 days is met, but do not go further than 3 days. I feel like I'm on the right track, but ...
2
votes
1
answer
46
views
Displaying items between 2 dates
I am creating a commission tracker for a sales team
Commission is payable on a new account on any business brought in for 3 months from the date of the first sale.
The accounts table will have the ...
- 43
0
votes
2
answers
93
views
MySQL YEARWEEK function is not using index
Given are two tables:
create table table_a
(
id_k mediumint unsigned not null,
id_m tinyint unsigned not null,
id_a char(10) not null,
v tinyint unsigned not ...
- 45
0
votes
1
answer
519
views
Incorrect datetime str_to_date INSERT SELECT
I have never run into such an issue before. Was given a bunch of data and told to compile it into some tables and develop some reports. The users who gathered the data used various different date ...
- 141
-2
votes
2
answers
303
views
Next 90 day review date (Teradata)
Teradata Related question… I have a table containing new starters along with a start date, every 90 days from the start date I need to conduct a review with the starter
How do I identify the next 90 ...
- 1
0
votes
0
answers
584
views
update primary key to a new one in sql server
I have got a table that has four columns and the Id is a Primary key:
Id
Matricule
Date
Code
G00183|2022-04-30|30
G00183
2022-04-30 00:00:00.000
30
G00183|2022-04-30|5010
G00183
2022-04-30 00:00:00....
0
votes
1
answer
38
views
Select entries with today and tomorrows date
I am using MariaBD and am trying to filter entries with todays date and tomorrows date.
I have managed to filter todays date using
SELECT *,
DateTime AS Time
FROM table
where status = '...
- 167
0
votes
1
answer
39
views
Finding the latest subscription date
I need to query a users database that contains record based on their subscription to a service. A user can have subscribed to it while buying the main product or have it added to their subscription ...
0
votes
2
answers
529
views
How to Select The Lowest Date in one field that's higher than the date in another field
The below aim isn't actually what my query is for, but I'm using it as an analogy to explain more simply what I am trying to achieve:
I am trying to build a BigQuery Script which looks at all arcade ...
4
votes
1
answer
1k
views
Aggregate overlapping date intervals
I have a table with some accounts, and their subscription's start and end dates. However, these subscriptions overlap sometimes and I need the start and end dates of every connected subscription ...
- 43
2
votes
0
answers
132
views
Past date shows for CURRENT_TIMESTAMP and GETDATE() and GETUTCDATE()
Why is that that suddenly in SQL Server Management Studio when I query CURRENT_TIMESTAMP and GETDATE() and GETUTCDATE(), it gives me a past date?
SELECT
SYSDATETIME() ,SYSDATETIMEOFFSET() ,...
victorio
0
votes
1
answer
804
views
Find Consecutive Date in SQL using 2 date columns
I am trying to group by all consecutive patient admission to the table. If the patient re-admitted to the hospital next day( or within 24 hours), then I need to keep only one admission stay per ...
- 1
-1
votes
3
answers
5k
views
How to select Max date that is valid?
I have a date as varchar (dont ask why). I need to select Max(date). However, date can be faulty, like 2034-34-34. I would like to select max date that is valid and not greater than today. It's t-sql.
...
- 133
1
vote
1
answer
43
views
Database Design: Year of Birth /Year of Death best practice - Postgres
I have a table of people (authors), and columns for Year_of_Birth and Year_of_Death. These are integers, as I wish to be able to compute things like current_age and age_at_death and ...
- 121
0
votes
1
answer
490
views
Why does PostgreSQL keep resetting my date to the wrong "timezone", with "seconds", as I try to remove it?
I asked this question at first: https://stackoverflow.com/questions/71979003/why-does-strtotime-get-confused-about-timezones-instead-of-ignoring-them-an
I have this timestamp with timezone column ...
- 99
4
votes
2
answers
1k
views
Why does this query fail when I run it from a SQL Server job?
I know how to solve the issue, but I don't understand the technicality of why the below query would work when I run it from SSMS, but the same query fails when I add it as a job in the SQL Server ...
- 90
0
votes
2
answers
4k
views
Postgres: How to automatically fix date/time field value out of range inside the query
How one can make Postgres treat a little stupid about the date which actually does not occur on the Gregorian calendar?
SELCET date, value
FROM table
WHERE date BETWEEN 2022-01-01 AND 2022-02-31
...
- 101
1
vote
0
answers
51
views
Splitting time period into multiple with irregular length, grouped by name
We have a set of data containing event dates, other event data and the start and end date that of the 'event season' that the event took place in.
The issue is as follows: In postgresql, I wish to ...
- 123
1
vote
1
answer
387
views
Splitting time period into multiple smaller ones with irregular length
We have a set of data containing event dates, other event data and the start and end date that of the 'event season' that the event took place in.
The issue is as follows: In postgresql, I wish to ...
- 123
-1
votes
1
answer
663
views
How to store date with only four digital format?
In postgresql the date format is yyyy-mm-dd by default.
create table sample(
quant int,year date);
The data file is stored data.csv in csv format such as:
quant,year
10,2019
12,2020
13,2021
Try to ...
- 284
1
vote
1
answer
162
views
Role-playing Dimensions
I am new to data warehouse schema and i am confused about role-playing dimensions. i have searched a lot and regarding to dates in data warehouse, it is always the same type of example they give ...
- 13
0
votes
1
answer
223
views
how to convert a string to date in MySQL 5.7
Now I have a data string like this 2002年05月11日, I want to convert this string as a unix date timestamp, what should I do to convert this time? I have tried this way:
select STR_TO_DATE("...
- 755
0
votes
1
answer
2k
views
Oracle date or timestamp PARTITION columns
Is there a query I can issue to tell if a PARTITION column is a date or timestamp.
For example this should retun timestamp
CREATE TABLE ts (
num_val NUMBER(6),
ts_val TIMESTAMP)
PARTITION BY RANGE (...
- 103
1
vote
1
answer
2k
views
SQL query display # of events per day between two time ranges
I have the following table:
id start_date end_date status
----------------------------------------------------------------------
1a 2020-04-09 21:34:00.000 ...
- 13
0
votes
1
answer
451
views
SQL query that is able to count days based on event queries
I have a table with the following data:
id date_changed color_start color_end
-------------------------------------------------------------------
1 2020-05-27 16:33:52....
- 13
1
vote
1
answer
997
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 &...
- 143
2
votes
1
answer
465
views
Is there a Rust crate that implements the SQL date and interval types?
I went looking for a Rust crate (on lib.rs) that implements DateTime and found chrono.rs. However, I have two issues.
I cannot find a function that takes a string representing an SQL date and ...
- 141
1
vote
0
answers
1k
views
Filling gaps in result with empty data
PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Hello All,
I'm a bit stuck on this one.
Working SQL
Currently I am using this query:
...
- 43
0
votes
1
answer
46
views
MariaDB 0th day?
Today is the 2nd of october, I added a line to my table using curdate() -2 and now it shows the 0th of October when it's supposed to show September 30th. Anyone know why this is happening?
- 35
0
votes
1
answer
49
views
Add an auto now field to an existing table
I want to add a new column created_datetime. This column will automatically be filled with a the current date/time value when inserting a new row.
How should I handle the value for existing rows. ...
- 111
0
votes
1
answer
71
views
Creating a daily Search Query for the previous day to today
I am trying to make a custom search query in maximo for equipment at work. I have a specific time range I want but it is always for the day before. Example: Today is 8/26/2021. I want a query for 8/25/...
- 3
4
votes
1
answer
476
views
SQL Server 2016 suddenly produces Error:402,State:1,Class:16 updating a sproc with DATETIME/DATE/TIME arithmetic, that was fine before
In one of stored procs I need to capture the duration of a live event. It is guaranteed to never exceed 24 hours, so the datatype for the result column is chosen as TIME(3) (mapping to TimeSpan on ...
- 151
0
votes
1
answer
84
views
Strange replication failure on date field under MySQL 5.7
This problem cropped up yesterday and I'm quite stumped. I have a primary RDS cluster that is running MySQL 5.6 and RDS is forcing everyone to 5.7 soon. I have a smaller standalone replication slave ...
- 103
0
votes
0
answers
45
views
using lead() to modify next row data
So I have some data like this:
And what I would like to do is modify the WorkDate using the following logic
if lead(EndTime) = StartTime
then lead(WorkDate) = WorkDate
So for example R3 WorkDate ...
- 1