Questions tagged [datetime]
for questions specific to data types that contain both date and time parts.
380
questions
0
votes
2
answers
132
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,039
0
votes
2
answers
50
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
17
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
113
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
15
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
47
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 ...
- 251
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
0
votes
0
answers
119
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)
...
- 437
-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
1
vote
1
answer
47
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
542
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
931
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....
- 499
1
vote
2
answers
217
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
229
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 ...
- 89
-1
votes
1
answer
470
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
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
4
votes
1
answer
346
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 ...
- 227
0
votes
2
answers
3k
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
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
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
164
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,601
1
vote
0
answers
264
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
93
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
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
868
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
2k
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
846
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
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
2
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....
- 21
3
votes
1
answer
7k
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
560
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,484
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
3
votes
2
answers
3k
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
741
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
174
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
352
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
418
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
2k
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,451
2
votes
1
answer
467
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
1
answer
8k
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
423
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
0
votes
1
answer
2k
views
Teradata min and max dates Find the Employees Start Date and Time and End Date and Time for each date
I have columns AssignedTo, InternalID, Task Function, Min(STARTDATE), Max(ENDDATE)
AssignedTo
InternalID
Task Function
STARTDATE
ENDDATE
Joe
67077
Write the letter
08/24/2017 05:30 AM
NULL
Joe
...
- 11
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
441
views
Given a table with date_time column, how to run a query using only the date
I have a DB with a table which has a column datetime Date_Time.
The format of this is for example: 2021-09-01 13:15:16.
I would like to set a query that is indipendent of the time but just consider ...
- 101
-1
votes
1
answer
73
views
How to catagorize timestamp based on hours in postgreSQL [closed]
I am trying to categorize the timestamp by the HOUR using the arrival column categories like ['00.00 - 06.00', '06.00 - 10.00', '10.00 - 14.00', '14.00 - 18.00', '18.00 - 00.00']). I am very new to ...
- 1
-2
votes
1
answer
187
views
How to extract variation values with MYSQL query
This is our situation:
We have a +50MM records.
The DB structure is as follows:
id Field: int(15) auto_increment,
DATETIME field,
METER field: varchar(15),
RECORD_A field: float(10,2)
Each METER ...
- 1
0
votes
0
answers
240
views
Postgres cast MI:SS formatted string to "interval minute to second" type
I have a data source with the time information being formatted like this: 'MI:SS'.
I am trying to coerce that to a native postgres INTERVAL or TIME type so I can leverage the equivalent operators.
I ...
- 31