Questions tagged [datetime]
for questions specific to data types that contain both date and time parts.
354
questions
-1
votes
3
answers
81
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
101
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
2
votes
1
answer
38
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 ...
- 175
6
votes
2
answers
520
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,404
1
vote
1
answer
38
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 ...
- 111
3
votes
1
answer
440
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
79
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 ...
- 103
1
vote
0
answers
56
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
195
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
...
- 249
0
votes
0
answers
44
views
Redshift extract function not being consistent
I'm trying to move Postgres(v 11) queries to redshift(v 8).
I encountered a problem with date_part.
When I try to run this, I'm getting the below error.
select date_part('hour', modified_on - ...
1
vote
1
answer
74
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
0
votes
2
answers
435
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'
'...
- 111
14
votes
1
answer
919
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,341
-1
votes
1
answer
78
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 ...
- 99
6
votes
1
answer
2k
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 ...
- 493
0
votes
1
answer
82
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 ...
- 177
1
vote
1
answer
845
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 ',' ...
- 21
0
votes
1
answer
275
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
223
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
55
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 ...
- 1
-1
votes
1
answer
35
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
35
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
33
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 ...
- 21
1
vote
1
answer
21
views
Convert specific time format to common one
Environment:
I have 'duration' datas stored as varchar(4) in a table.
2 first characters are hours, 2 last characters are minutes. E.g.:
0100 means 01:00
0456 means 04:56
and so on...
What I need:
...
- 113
6
votes
1
answer
1k
views
SQL Server 2012 equivalent of AT TIME ZONE capability
Is there a way in SQL Server 2012 to get what the server time zone would be on a particular date/time? In SQL Server 2016, you have AT TIME ZONE, but that's not available in 2012. I have a ...
- 6,592
0
votes
1
answer
245
views
Stored procedure to change string to datetime in MySQL
This is a follow up to this question. Turns out the driver that I am using (can't change it) is not compatible with timestamp values. Lets say it is set in stone that I have to load the data into my ...
- 105
0
votes
2
answers
184
views
Does a NULL DATE use as much storage as a value?
I need to store a DATETIME value that may or may not have a date.
My options are:
| starts DATETIME |
|---------------------|
| 0000-00-00 12:38:06 |
| starts_date DATE | starts_time TIME |
|----...
- 121
2
votes
1
answer
156
views
Office Hours in PostgreSQL
How to store office hours in PostgreSQL rows, so that I can calculate the office hours.
Example:
We have open from 9:00 to 18:00 from Monday till Friday.
Saturday we have open from 10:00 to 15:00
...
- 1,331
0
votes
1
answer
144
views
What exactly is the datatype input into a DATETIME value in MySQL
I am trying to get data from a PLC directly into a MySQL 8.0 database (using MySQL Workbench). I need to know what data type the input value is for a column set to DATETIME so I can send in the right ...
- 105
4
votes
1
answer
3k
views
Parsing DATE while copying csv file into PostgreSQL table
I have a long series of .csv files, which I want to import into a local database. I believe my query is correct, but there are some problems in parsing DATE and TIMESTAMP columns. PostgreSQL reads ...
- 143
0
votes
1
answer
487
views
Converting DATE and TIME integers to timestamp
I am trying to get a timestamp by combining date and time columns in my PostgresQL database and am running into errors. Here is what I have:
+-----------+-----------+
| StartDate | StartTime |
+-------...
- 101
13
votes
4
answers
3k
views
In the same query INSERT, can two VALUES being NOW() return a different time?
I met a query similar to
INSERT INTO mytable (id, Created, Updated) VALUES (null, NOW(), NOW())
having column definitions
| Created | datetime |
| Updated | datetime |
Will in this case MySQL ...
- 395
1
vote
2
answers
2k
views
"1292 Incorrect datetime value" when updating an "empty" datetime field
I have an updated_at field that was not initially set with default datetime value on the server. For a short period, a bunch of records got inserted with an "empty" value and are showing up ...
- 11
0
votes
3
answers
134
views
How to re-write this query or restructure my table for SPEED
I have an InnoDB MySQL table of this structure:
column
type
service
varchar(16)
url
varchar(255)
datetime
datetime
It contains logs for requests made to one of multiple services. It contains ~3 ...
- 111
0
votes
0
answers
476
views
Compare datetimes with dfferent timezones in MySQL
I have a field `opened_at` in MySQL table that stores datetimes in string format with different timezones:
2021-01-30T18:07:40+02:00
2021-01-31T01:32:40+05:00
2021-01-31T21:17:40+03:00
etc.
I need to ...
- 101
1
vote
1
answer
37
views
Mealplan: Allow ordering until 9am
Imagine you have a meal service and everyday there is a different meal which customers can order.
Mon, Dec 12: Pasta
Tue, Dec 13: Pizza
Wed, Dec 14: Gnocci
Orders are possible until 9am.
This mean ...
- 1,331
1
vote
2
answers
739
views
How to query time series with gaps between a given date range in PostgreSQL?
My problem is I don't know how to query time series data between a specified date range where the data has gaps in it and the last known value which should carry forward for each date falls outside ...
- 121
8
votes
2
answers
358
views
Inability to query underlying table of a schema-bound view
I migrated a database from SQL Server 2008R2 to SQL Server 2019 (both Enterprise Edition) using the backup-restore technique, and a set of post-transfer actions like DBCC UPDATEUSAGE or UPDATE ...
-2
votes
1
answer
99
views
Convert Varchar to Datetime
I have a database field Column_A which is in Varchar(16) not null and the values are for example 20191210215033CS but I want to break it like 2019-12-10 21:50:33
Since it is a production database ...
- 1,007
4
votes
1
answer
859
views
SQL Server with French_CI_AS Collation date conversion
I have a French installation of SQL Server using French_CI_AS Collation. For legacy reasons, a table column stores data as VARCHAR which is later converted to the right type, akin to variant in C++.
...
0
votes
1
answer
56
views
DateTime conversion fails when the datetime has seconds [closed]
This seems to be most discussed, but I could not arrive at a solution yet. I have a date format and have to add a month to it (DATEADD should not be used as my date is string initially) and finally ...
- 3
5
votes
1
answer
516
views
DATEADD and DATEPART with milliseconds yields strange results
I have a table containing the date in unix time and a separate milliseconds field. I now try to create a date from the two fields for later calculation (e.g. filtering on a time range). After adding ...
- 155
0
votes
1
answer
33
views
Strange max(date) return in reader vs writer aurora mysql [closed]
Something very strange happening here which I can't point out. Using AWS Aurora, select max(date) in the writer gives me one date, in the reader it returns strange date and warnings.. Column is ...
- 393
1
vote
1
answer
443
views
Subtract column value from date in days or months
I want to subtract the value in column lag_days from current_date.
Usually I would do current_date - interval '1 day'
I'm getting syntax error on the following example to subtract by days:
dkarchive=&...
- 393
0
votes
2
answers
58
views
Can somebody please tell what is the difference between these two date comparisons?
So I have 2 queries as follows:
f.DATE >= (GETDATE() - 3)
f.DATE >= CAST(CONVERT(VARCHAR(10),GETDATE() - 3,101) AS DATETIME)
I think they are the same but the second one gives me more rows ...
- 1
2
votes
1
answer
979
views
Indexing a date column in MySQL doesn't work if a date function is used
I have a table with the following schema
table_name
id int
task_id int
completed_date datetime (INDEX IX_TBL_NAME_COMPLETED_DATE)
I run EXPLAIN on this query
EXPLAIN
SELECT
*
FROM ...
- 123
0
votes
1
answer
36
views
How to insert '1001-01-01 00:00:00' into MariaDB when altering a table?
I created a table in HeidiSQL:
CREATE TABLE `publish_dt` (
`ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
`TS` DATETIME NULL DEFAULT NULL,
`Text` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci'...
- 115
5
votes
2
answers
242
views
Datetime progressive sum PostgreSQL
my first question here, but I looked in many other places and could not find the answer to my problem.
id | track_seg_id | time
----+--------------+---------------------
1 | ...
- 153
1
vote
3
answers
932
views
I want to get all the weeks of a month with the start and end dates of those weeks without creating a table
My SQL is:
SELECT date_trunc('week', '2020-06-01'::timestamp)::date ||'-'||
(date_trunc('week', '2020-06-01'::timestamp)+ '6 days'::interval)::date;
However, using this code it shows only one week'...
- 11
0
votes
1
answer
28
views
Pivot on datetime with matching multiple column
I have the following table with column school, state, subject and timedate.
I would like to have output with column school, state, act and english
filter subjects to only Act and English
pivot table ...
- 1