Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

Questions tagged [datetime]

for questions specific to data types that contain both date and time parts.

Filter by
Sorted by
Tagged with
-2 votes
1 answer
45 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 ...
Elikill58's user avatar
  • 221
4 votes
3 answers
1k 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 -&...
questionto42's user avatar
0 votes
1 answer
77 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 ...
J. Mini's user avatar
  • 401
0 votes
1 answer
49 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 ...
Carolina's user avatar
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 "...
VBStarr's user avatar
  • 109
3 votes
2 answers
718 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 ...
agerber85's user avatar
  • 111
0 votes
2 answers
285 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 ...
SQL_NoExpert's user avatar
  • 1,061
0 votes
2 answers
99 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 ...
Sam Taylor's user avatar
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....
Stanislav Bashkyrtsev's user avatar
0 votes
0 answers
20 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 = '...
Fausto Galati's user avatar
0 votes
1 answer
1k 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 ...
codki's user avatar
  • 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 ...
Ricardo's user avatar
0 votes
1 answer
87 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 ...
Charlie Clark's user avatar
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 ...
Fajela Tajkiya's user avatar
0 votes
0 answers
157 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) ...
HeyJude's user avatar
  • 447
-2 votes
1 answer
200 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 ...
Raj's user avatar
  • 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 ...
DanMossa's user avatar
  • 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 ...
GaTechThomas's user avatar
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....
gavenkoa's user avatar
  • 509
1 vote
2 answers
303 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 ...
CopOnTheRun's user avatar
0 votes
2 answers
275 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 ...
Viz Krishna's user avatar
-1 votes
1 answer
766 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 ...
baxx's user avatar
  • 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 ...
Paulmcf1987's user avatar
5 votes
1 answer
470 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 ...
Jeremy S.'s user avatar
  • 237
0 votes
2 answers
5k 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 ...
uk_user2014's user avatar
-2 votes
2 answers
331 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 ...
Spindo82's user avatar
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: ...
Tobias Leupold's user avatar
1 vote
3 answers
195 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,...
Jonathan Fite's user avatar
1 vote
0 answers
343 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), (...
adius's user avatar
  • 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 ...
WhoIsNotActive's user avatar
0 votes
1 answer
61 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 = '...
PaulMcF87's user avatar
  • 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 ...
user3118602's user avatar
-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 ...
Mark de Wet's user avatar
-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: ...
pgscls's user avatar
  • 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. ...
Hrvoje T's user avatar
  • 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....
Gulfhawk's user avatar
3 votes
1 answer
9k 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 ...
ffox003's user avatar
  • 185
6 votes
2 answers
562 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 ...
Vaccano's user avatar
  • 2,496
0 votes
1 answer
572 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 ...
Homer's user avatar
  • 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') ...
RDD's user avatar
  • 33
0 votes
1 answer
872 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 ...
user3118602's user avatar
1 vote
0 answers
178 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 ...
user2855892's user avatar
5 votes
2 answers
358 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 ...
Rushabh Mehta's user avatar
1 vote
1 answer
459 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 ...
user avatar
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' '...
Eric Lee's user avatar
  • 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-...
Artashes  Khachatryan's user avatar
2 votes
1 answer
552 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 ...
intel_chris's user avatar
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 ...
w0051977's user avatar
  • 503
0 votes
1 answer
462 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 ...
roy naufal's user avatar
1 vote
1 answer
4k 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 ',' ...
Sonu R's user avatar
  • 31

1
2 3 4 5
8