Questions tagged [datetime]
for questions specific to data types that contain both date and time parts.
388 questions
-1
votes
1
answer
36
views
Joining with generate_series for missing dates with derived date column
There are a few similar questions to this (e.g.https://dba.stackexchange.com/questions/72419/filling-in-missing-dates-in-record-set-from-generate-series) but the solution does not appear to work in my ...
- 1
0
votes
2
answers
81
views
Datetime2 in SQL 2014 Vs SQL 2022
Behaviour of column type 'DateTime2' between SQL 2014 and SQL 2022.
This was found when our application's calculation for datetime difference of columns being type DateTime2 went wrong.
Tried to find ...
- 81
5
votes
1
answer
588
views
MSSQL - query runs for too long when filtering after a certain date
So I would like to understand a concept of MSSQL that is a bit interesting to me, but I didn't really find any answers online until now.
I have a query that runs on a 100mil+ rows table. Lets say that ...
- 61
9
votes
2
answers
805
views
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
, ...
- 31.9k
11
votes
2
answers
1k
views
Why is a datetime value returned that matches the predicate value when using greater than comparison
I want to find any records greater than the time supplied in my predicate, which is coming from a C# web application in which there is only type DateTime. However, SQL Server is returning a row that ...
- 525
1
vote
0
answers
9
views
Tool/library/function creating time-interval/state-duration report from a time/state series
I am looking for a standard method, a library or maybe best practice approach that can generate a time-interval/state-duration report from a time/state series.
This seems like a common reporting ...
- 11
-2
votes
1
answer
63
views
Why is it selecting date from another month that requested one? [closed]
I have a table that store time begin and time end of some events. I want to search in this table all even that were occuring during specific day.
Here is example data:
Id
Date begin
Date End
Name
1
...
- 181
4
votes
3
answers
2k
views
Integer number in the 700000s as the days from year 1: how can this be cast in tsql to a date and back if the oldest datetime date is 1753-01-01?
I fell upon an integer format for dates for which I also know the date, but I do not know how to get to that date in TSQL and I also do not know how to get to the integer if I have the date:
700444 -&...
- 336
0
votes
1
answer
116
views
How does the arithmetic for how many bytes are saved by row-compressing a datetime add up?
Everything that follows is quoted from the datetime row here. I have broken the quote to give commentary on my understanding.
Uses the integer data representation by using two 4-byte integers. The ...
- 799
0
votes
1
answer
76
views
Inner join by datetime columns
I'm working in restructure a partinioned table so I have two tables with the same schema, for ensuring the insert process was executed succesfully without change some value i want to validate that the ...
- 49
0
votes
1
answer
53
views
MySql TIMESTAMPDIFF in Update fails
I'm using MariaDB 10.4.25 and am trying to update some data in a WordPress table, but getting an error.
I can run the SELECT query without a problem, but then when I run the UPDATE, it throws a "...
- 109
4
votes
2
answers
1k
views
Why is SQL server dropping the seconds of values passed to a SMALLDATETIME field?
I'm using
Microsoft SQL Server 2019 (RTM-CU22) (KB5027702) - 15.0.4322.2 (X64)
Jul 27 2023 18:11:00 Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Windows 10 Pro 10.0 (Build ...
- 121
0
votes
2
answers
422
views
Convert varchar into datetime and use date diff on the where clause
I have a field ABCD stored as varchar(16) and the data stored as 20170509074744CD.
I am not versed with SQL Language. I will greatly appreciate any help converting the field ABCD into datetime and use ...
- 1,063
0
votes
2
answers
219
views
Pull Datediff without year in SQL Server
In SQL Server, I want to pull a List of dates that are 2 days before/30 days after current date, but ONLY the month/Day matter, not the year.
For instance today is 10/31/2023
I would want 10/30/1980 ...
0
votes
1
answer
2k
views
Postgres changing data type from timestamp to timestamptz (with time zone)
I recognize there are some similar questions, and I've read quite a few, so pardon me if I just missed the answer.
I have data that was stored as a TIMESTAMP data type, but now we want it to be stored ...
- 11
0
votes
0
answers
17
views
Return nothing or all if overlaps fit somewhere PSQL
I have searched a bit and not come across anything comparable, so I'm asking this question here, hoping I haven't overlooked anything.
In my situation, we store deliveries on the database, which ...
- 1
0
votes
1
answer
137
views
Performance of (functional) indices for datetimes and window functions in Postgres
I think this is a fairly standard type of task but I haven't seen anything covering performance in detail, though Index Optimization for Datetime comparing day of week and hour is pretty close. From ...
- 291
3
votes
5
answers
2k
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,219
-2
votes
1
answer
276
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
1
vote
1
answer
68
views
Possible to order column in range of another column?
I have a table with the following two columns: about and create_time.
I want to receive a list of rows that are ordered length(about) DESC but only per chunk of time. For example:
If I have the ...
- 145
7
votes
1
answer
2k
views
Stuffing a datetime value into a datetime2 column
Today I saw the following statement in a code review:
ALTER TABLE dbo.MyTable ADD CreateDate DATETIME2(0) NOT NULL DEFAULT GETUTCDATE()
The GETUTCDATE() function returns a datetime and is then ...
- 785
0
votes
1
answer
2k
views
Does changing server time zone "shift" SQL Server stored date / time values in ODBC / JDBC?
I've read that SQL Server:
uses system time zone on Windows (reg key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\TimeZoneKeyName)
uses TZ env var on Linux or /etc/timezone....
- 519
1
vote
2
answers
386
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
0
votes
2
answers
324
views
How to convert getdate() stored as varchar to date
I have stored 'getdate()' as text in one of my column in a table. (It's a complicated scenario ,how I saved it)
Now when I select the field what I am getting is getdate() instead of the date . How can ...
- 99
0
votes
1
answer
1k
views
Why does the now() function return the same value for multiple inserted rows?
Perhaps there's a more idiomatic approach to this in Postgres, but the pattern is reasonably common so I expected it to work.
Here's the code:
drop table if exists tbl;
create temporary table tbl as ...
- 316
2
votes
1
answer
51
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
5
votes
1
answer
610
views
Likelihood for Discrepancy in GETDATE() Values Within UPDATE Statement
As the title alludes to I am wondering if it is possible or if you have experienced an issue with differing GETDATE() results within the same UPDATE statement.
I have a stored procedure which updates ...
- 237
0
votes
2
answers
6k
views
how do I select all records that have an event datetime within 1 hour before
how do I select all records that have an event datetime within 1 hour before, for example mysql select all that time_diff less than 1 hour from datetime? its for a reminder email i want to make on a ...
-2
votes
2
answers
369
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
3
votes
2
answers
3k
views
Retrieving the local timezone inside SQLite
I have an SQLite table containing a "last changed" column with a date in the format e.g. "2022-11-07T11:51:06+01:00". Coreutils' date outputs this by using the following command:
...
- 137
1
vote
3
answers
226
views
Finding the Second (time) Interval within X of the current time
I have a need to find, for a given current second and upper and lower boundary, find all of the seconds that would fall within that, wrapping at the 59 second mark.
With that word salad out of the way,...
- 9,244
1
vote
0
answers
408
views
Resample time series in SQLite
I have following table:
CREATE TABLE entries (
utc TEXT,
entry INTEGER
);
with following data:
INSERT INTO entries
(utc, entry)
VALUES
('2022-10-01 01:54', 23),
('2022-10-04 02:40', 46),
(...
- 113
0
votes
1
answer
152
views
Compare table datetime, SQL & SSIS
I am trying to add a step in my SSIS job where it checks a table (FYI, so this table has only 1column which is a datetime data type that gets updated daily with a datetime from another table) whether ...
0
votes
1
answer
94
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 = '...
- 177
0
votes
1
answer
2k
views
Handling multiple dates on fact table with performance concerns
I am working on a simple data marts and has once again stumbled with the handling date and time.
This is a design on a vehicle maintenance form records to collect the status record/remarks for ...
- 147
-1
votes
1
answer
8k
views
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value when running T-SQL query
Please note I am not a coder by any means, I am simply trying to assist with a migration from SQL Server 2008 R2 and 2008 to SQL Server 2022 and 2019.
When I execute the following T-SQL query:
select ...
- 13
-1
votes
1
answer
1k
views
DateTime Syntax Error [closed]
I am building a very basic database in SSMS, one table of which will hold "sleep instances" recording the time of falling asleep and waking up. I have created the table with three columns: ...
- 1
-1
votes
3
answers
6k
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.
...
- 163
3
votes
1
answer
3k
views
How do I create a SQL loop that generates future dates based on different frequencies and intervals for each item until a specified end date?
I have a data set that appears like this:
next_generation_date
procedure
interval
frequency
2021-01-17 00:00:00.000
Clean Restroom
1
day
2021-01-17 00:00:00.000
Vacuum
2
week
2021-02-01 00:00:00....
- 31
3
votes
1
answer
10k
views
How to query dates in different timezones?
I have a table and index in a PostgreSQL 10.18 database:
CREATE TABLE some_table (
expires_at timestamptz
);
CREATE INDEX ON some_table(expires_at);
Is there a way to write this query in a way to ...
- 185
6
votes
2
answers
565
views
DateTime2 and Page Life Expectancy (PLE)
As I understand it, when you define a column on a table you define its precision. This precision takes 1 byte and is stored at the column level. If you use a precision of 5 or more, then a DateTime2 ...
- 2,540
3
votes
2
answers
4k
views
Convert Date format to insert into MySQL database
I'm receiving the date in 'Sun Jun 20 00:40:27 IST 2021' format. Which I need to insert to my MySQL database in datetime(6) format.
I used
STR_TO_DATE('Sun Jun 20 00:40:27 IST 2021','%d/%m/%Y %T')
...
- 33
0
votes
1
answer
1k
views
How to handle incoming time-series data in a data warehouse?
I am new to data warehousing (having learnt this in my school days) and is looking to do a data warehouse as a side project. Below is a simple data warehouse design I came up with:
The data warehouse ...
- 147
1
vote
0
answers
190
views
Returning overlapping rows with same state based on Id
I have a table with internal_id, states, start time and end time. I am trying to overlay the recent state to the previous states and return the value.
I have a simple working example here which works ...
- 11
5
votes
2
answers
371
views
Computing the set difference of tables of intervals
I often run into the following problem. I have two tables of intervals. They are bounded by dates (with no time component). Within each table the intervals do not overlap.
StartTs
EndTs
2015-01-03
...
- 251
1
vote
1
answer
506
views
Storing timezoned HH:MM in PostgreSQL
My application needs to send a daily email to users at a time of their choosing, the timezone is also defined by the user itself.
What's the best way to store HH:MM + timezone? And how can I ...
user244938
1
vote
2
answers
3k
views
MySQL: Is There a way to dynamically generate multiple date range based on its date
The data I got is
id
date
cnt
1
'2021-11-26'
1
1
'2021-11-25'
1
1
'2021-11-24'
1
1
'2021-11-23'
1
The result I want is
id
from_date
to_date
cnt
1
'2021-11-23'
'2021-11-24'
2
1
'2021-11-23'
'...
- 121
15
votes
2
answers
3k
views
Incorrect comparison of datetime and datetime2
I know that it is not a good practice to have an implicit type convert. But this is really unexpected behavior when a lower value can suddenly become higher.
declare @LastSelectedDate DATETIME = '2021-...
- 1,533
2
votes
1
answer
644
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
7
votes
2
answers
11k
views
Why is GetDate AND GETDATE() AT TIME ZONE 'GMT Standard Time' returning the wrong time?
Please see the code below:
DECLARE @UKDateTime as DateTime
SELECT @UKDateTime = GETDATE() AT TIME ZONE 'GMT Standard Time'
print @UKDateTime
PRINT GETDATE()
This returns:
Oct 4 2021 4:03PM
Oct 4 ...
- 513