Questions tagged [datetime]
for questions specific to data types that contain both date and time parts.
323
questions
0
votes
3answers
51 views
MSSQL - Convert overlaping datetime ranges into non-overlapping datetime ranges with respect to priorities
I have data stored in a table like this:
CREATE TABLE Records
(
[Id] int IDENTITY(1,1) not null
,[From] datetime not null
,[To] datetime not null
,[Priority] int not null
)
Every row contains ...
0
votes
0answers
19 views
When you use English time strings in PostgreSQL, does it base it on the current timestamp or the previous midnight? [closed]
Suppose I have this query:
SELECT * FROM table WHERE timestamp < now() - INTERVAL '3 weeks';
Will this return all records which have a timestamp older than:
"right now, minus the average ...
0
votes
1answer
28 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 ...
0
votes
2answers
44 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 ...
8
votes
2answers
283 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
1answer
49 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 ...
3
votes
1answer
148 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
38 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
221 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
17 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
44 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
35 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
229 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
43 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
32 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
16 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
17 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
36 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
34 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
57 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
132 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
41 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
134 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
53 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
138 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
80 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
75 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 '2019-...
0
votes
1answer
2k 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
314 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
21 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
92 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 ...
1
vote
1answer
433 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
92 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
62 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
36 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
294 views
Select youngest record
I have a MariaDB table "mdata" with the following schema:
+-----+-----------+---------------------+------+
| id | room | date | temp |
+------+-----------+---------------------...
2
votes
1answer
53 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
96 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
959 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 ...
