Questions tagged [datetime]
for questions specific to data types that contain both date and time parts.
386
questions
0
votes
1
answer
14
views
Why is it selecting date from another month that requested one?
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
...
- 225
1
vote
3
answers
64
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 -&...
- 234
0
votes
1
answer
65
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 ...
- 139
0
votes
1
answer
46
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 ...
- 37
0
votes
1
answer
29
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
3
votes
2
answers
657
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 ...
- 111
0
votes
2
answers
267
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,049
0
votes
2
answers
86
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
0
answers
28
views
PG timezone offsets inverted? [duplicate]
I can't understand this behavior in Postgres 15:
set timezone = 'UTC-4';
select now(), cast(now() as timestamp without time zone)
I get: 2023-10-26 13:47:45.202867 +04:00 and 2023-10-26 13:47:45....
0
votes
0
answers
19
views
sp_get_query_template doesn't work with ODBC timestamp
I have the following query:
SELECT COUNT(*) FROM (SELECT DISTINCT USER_NAME FROM LICENSE WHERE RESTRICTED_USE = 'T' AND ALLOCATED = 'T' UNION ALL SELECT USER_NAME FROM LICENSE WHERE RESTRICTED_USE = '...
0
votes
1
answer
751
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
80
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 ...
- 271
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,169
0
votes
0
answers
145
views
Using CONVERT for datetime 24 hours with style codes undocumented for?
Using CONVERT for datetime whose time part is 24 hours - works. Examples with style codes 101-105:
SELECT CONVERT(datetime, '05/24/2023 23:30', 101)
SELECT CONVERT(datetime, '2023.05.24 23:30', 102)
...
- 447
-2
votes
1
answer
191
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
51
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
6
votes
1
answer
1k
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 ...
- 765
0
votes
1
answer
1k
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....
- 509
1
vote
2
answers
277
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
266
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
-1
votes
1
answer
721
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 ...
- 296
2
votes
1
answer
48
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
444
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
4k
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
324
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
1
answer
2k
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
187
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,...
- 8,666
1
vote
0
answers
336
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
103
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
59
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
1k
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
4k
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
2k
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
8k
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
561
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,496
0
votes
1
answer
543
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
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
851
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
176
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
357
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
453
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
2k
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
1
answer
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,461
2
votes
1
answer
543
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
9k
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 ...
- 503
0
votes
1
answer
455
views
select time intervals from a nested query
I have a table which has the following columns:
id, start_timestamp, device_id
I am trying to get results of all start_timestamps within 5 seconds of a range of generated periodic timestamps.
For ...
- 187
1
vote
1
answer
3k
views
Incorrect datetime value while importing in to MySQL table
I'm facing an issue while importing a .csv file into a MySQL table.
mysql> LOAD DATA INFILE '/var/lib/mysql-files/script_output.csv'
REPLACE INTO TABLE incidents columns terminated by ',' ...
- 31