Questions tagged [datetime]
for questions specific to data types that contain both date and time parts.
317
questions
3
votes
1answer
94 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
1answer
37 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 ...
4
votes
1answer
217 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 ...
0
votes
1answer
26 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 ...
1
vote
1answer
14 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=&...
0
votes
2answers
43 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 ...
2
votes
1answer
34 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 ...
0
votes
1answer
19 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'...
5
votes
2answers
224 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 | ...
1
vote
3answers
41 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'...
0
votes
1answer
22 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 ...
-2
votes
1answer
31 views
Function to convert time difference between two dates into timer format
Does anyone have a function that converts date difference between two dates into timer format ?
For example:
select udf_DateDiffTimer ('2020-05-12 14:12:34','2020-05-12 14:12:35')
The result should ...
0
votes
1answer
14 views
Querying the most recent records with two fields: datestamp and timestamp
I have a table called airquality that has records for air quality measurements taken on different days and times within those days. These date/timestamps are stored in two different columns: validdate ...
1
vote
1answer
16 views
Storing companies' working hours in Postgres
I have table with millions of companies data (name, address, phones etc) in my Postgres database. I want to store working hours (example: mon-fri: 9:00-12:00, 13:00-18:00; sat: 9:00-13:00). How can I ...
0
votes
1answer
31 views
How do I join on a timestamp and include NULL entries?
I am attempting a query to compare the sum of daily accounting invoices to a daily budget. The budget is stored as one value for a month, so I divide that amount by the total number of days as in ...
0
votes
1answer
26 views
Aggregating data by group and time gaps
I've got a postgresql+postgis database filled with GPS data:
CREATE TABLE "my_table" (
"id" serial NOT NULL PRIMARY KEY,
"gps_id" text NOT NULL,
"lat" numeric NOT NULL,
"lon" ...
-2
votes
2answers
53 views
MySql : Does it worth to convert a column with type DATETIME to DATE?
At the start of a project, a DATETIME column made sense. But now, 5 years after, requierments changed the time part have never be used.
My table has about 100k lines and grows with about 500 lines ...
0
votes
1answer
75 views
Invalid default value for DATETIME column?
I'm trying to execute my DDL.
I'm using docker-compose.
version: '3.3'
services:
mysql:
image: mysql:latest
# command: ['mysqld', '--character-set-server=utf8mb4', '--collation-server=...
0
votes
1answer
40 views
Select records between different work shifts depending on datetime
I have a data table called "Requests" that records the requests made by employees in a company, I used to use a Stored Procedure to get all the request from a single workday (starting at 6am and ...
0
votes
0answers
109 views
How does MySQL index a datetime column?
My assumption is datetime values, storing time to at least the second (YYYY-MM-DD HH:MM:SS), would be nearly unique across records in a database table.
For example, if there were three records in ...
0
votes
0answers
40 views
Date confusion - Postgres and Microsoft, when does Anno Domini start?
I have postgres table with a timestamp field.
I've noticed that it contains data that's shown as -
"0001-01-01 00:00:00 BC"
I'm trying to figure out were this data is coming from (it's NOT the ...
1
vote
1answer
81 views
Update record with converted epoch timestamp from another column
I'm trying to convert epoch data in one column (timestamp_epoch) to a legitimate timestamp in another column (timestamp) for every record in a table. In reading the PostgreSQL Documentation it ...
0
votes
0answers
56 views
fixing broken convert_tz() in Mysql?
I come across the documentation for convert_tz() in MySQL and it says
If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs. ...
2
votes
1answer
67 views
Date time cast preferred method
What would be the preferred method for speed and less database stress using a between or less than?
SELECT LogId
FROM [Master].[dbo].log mlog
WHERE cast(LogDate as Date) BETWEEN '2019-01-01' AND '...
0
votes
1answer
1k views
Select where datetime is greater than other datetime which may not exist
I have a table with a field eventtime of type datetime and a different table with a datetime field.
I need the minimum eventtime that is greater than the greatest datetime field from the other table ...
0
votes
1answer
25 views
Time interval is returning all records or no records
I am trying to run a query to return records in the last 24 hours and following the instructions here: time interval tutorial
After running the query at 18:30 UTC, my query looks as follows:
RETURN ...
0
votes
1answer
233 views
Disable mysql_tzinfo_to_sql during MariaDB/MySQL installation
MariaDB run in docker container hangs during initial startup while mysql_tzinfo_to_sql running because of the issue.
I've disabled mysql_tzinfo_to_sql by setting MYSQL_INITDB_SKIP_TZINFO env. var to ...
0
votes
1answer
20 views
I have hard time understanding Postgresql OVERLAP function when provided Interval
On postgresql documentation whilst reading for date and time operations I have hard time understanding the following example:
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
(DATE '...
0
votes
2answers
77 views
Why can't I insert this value using timestamp but can with datetime?
I want to insert this value: 2020-03-29 01:00:00
If I set up a column called date with data type datetime I can insert the value. But if I use the data type timestamp I cannot insert and get an error ...
0
votes
1answer
17 views
Does range improve match operation and which runs first, range or match
I am using MySQL version 5.6.10
I have a very very large table large_table and a relatively small table small_table
The small_table has only the records of the date 2019-11-20
There is a column I ...
0
votes
1answer
35 views
Locating all records relevant for a specific date
I have a MySQL InnoDB table with about 1GB of records and growing.
each record in this table has, among other fields two date fields :
AddedDateRecord - The date the record was added (datetime).
...
0
votes
2answers
49 views
How to add time from a Date value to another without a varchar conversion?
Let's say I have 2 variables in a Stored Procedure, both are Dates
Foo contains '13/10/2019 00:00:00'
Bar contains '01/10/1914 16:33:11'
I want to add Bar's time to Foo's date, so that I get a ...
0
votes
1answer
303 views
Dates values displayed 0000-00-00
I have imported a csv file into my database created in phpmyadmin using sql.
Server version: 5.6.37 - MySQL Community Server (GPL
I have a column for dates which has a datatype of date. The data ...
0
votes
1answer
81 views
TIMESTAMP OR DateUpdate
I want to implement incremental ETL on some of my sql server tables, so I need a new column like UpdatedDate to track change dates of my records. I want to transfer only updated and inserted records, ...
2
votes
1answer
31 views
Add Gameweek Fixeture based on Datefield
having the following table i need to add a new Column called "Round" to represent Round 1, Round 2 and so on, based on the Date Column in the table. Same Round should be given to matches happening in ...
0
votes
2answers
55 views
How to interpret timestamps stored as 10-digit negative integers?
I've inherited an undocumented MS Access database where the date/time values are stored in an INT column and the majority of values I've seen so far are 10-digit negative integers.
How do you ...
0
votes
2answers
35 views
Group results from two different tables on same hours
I have two simple tables:
indoor
id | timestamp | temp | humi
outdoor
id | timestamp | temp
And two selects which give me time and average temperature grouped by same hour for the last 24 hours:
...
0
votes
3answers
250 views
Select youngest record
I have a MariaDB table "mdata" with the following schema:
+-----+-----------+---------------------+------+
| id | room | date | temp |
+------+-----------+---------------------...
2
votes
1answer
52 views
How to combine date and time with a string value in column created by Case in SQL Query
I am in the process of creating a query for an SSRS report that will show a column that has DATETIME values. The column can have NULL values. In my query I would like to use a CASE expression.
I ...
0
votes
1answer
66 views
Best way to put date range in a table in mySQL
I'm designing a system which is basically a booking engine. it uses separate table to store room data and separate table to store it's availability information (available date(s) ). I'm stuck in ...
15
votes
5answers
8k views
How to add 1 milliseconds to a datetime string?
Based on a select, I can return x rows like this:
1 2019-07-23 10:14:04.000
1 2019-07-23 10:14:11.000
2 2019-07-23 10:45:32.000
1 2019-07-23 10:45:33.000
We have all milliseconds with 0.
Is ...
6
votes
1answer
874 views
SQL Server error 242 with ANSI datetime
I'm trying to debug a MS SQL Server error 242:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The error is originated by this statement:
...
2
votes
0answers
44 views
What is the name of month 0?
MySQL permits you to store a zero value of '0000-00-00' as a “dummy date.”: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html
I'm currently inserting into this field using STR_TO_DATE(?...
0
votes
1answer
56 views
Row for each date, start and end
I have a table that has two columns, startDate and endDate. Each day has its own capacity. Between these dates, user can get from 1 to n items. I am going to get number of items remaining for each day ...
0
votes
1answer
27 views
How to summate weekly totals from a postgres date range type per user
I have the following table:
Schedules
+----+---------+------------+-------+--------------------------+
| id | user_id | project_id | hours | date_range |
+----+---------+------------+---...
3
votes
2answers
7k views
Incorrect datetime value Mysql
Good morning.
Mysql 5.7.25:
insert into mytable values('2019-03-31 02:06:29')
return error "#1292 Incorrect datetime value"
but:
insert into mytable values('2019-03-31 03:06:29')
return no ...
4
votes
1answer
546 views
Check if two datetimes are between two others
Today I'm facing with problem, which I can't solve.
I need to check if two datetimes (2019-04-08 13:30:00 - 2019-04-08 15:30:00 ) are between or overlap two others (2019-04-08 14:00:00 - 2019-04-08 ...
-1
votes
1answer
68 views
mysql_query expected [closed]
currently I'm getting the error stated above using thise code:
$Tijd = date_create($_POST ["Tijd"]);
$Tijd->format(\DateTime::ISO8601);
$result = mysql_query ("SELECT Voorstellingsnummer FROM ...
1
vote
1answer
196 views
How to get all dates belonging to a timestamp range
I have a tsrange as input. For example:
tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp)
I want to generate all dates belonging to this range.
I started with the following, ...
0
votes
1answer
690 views
Pass 'interval' value to date_trunc function in PostgreSQL procedure
I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns.
The syntax in the ...
