Questions tagged [datetime]
for questions specific to data types that contain both date and time parts.
328
questions
0
votes
1answer
34 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 ...
0
votes
2answers
38 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 |
|----...
1
vote
1answer
46 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
...
0
votes
1answer
38 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 ...
0
votes
1answer
76 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 ...
0
votes
1answer
46 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 |
+-------...
10
votes
4answers
2k 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 ...
1
vote
2answers
132 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 ...
0
votes
3answers
46 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 ...
0
votes
0answers
44 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 ...
1
vote
1answer
31 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
123 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
294 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
63 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 ...
4
votes
1answer
337 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
42 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
250 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
27 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
40 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
49 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
55 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
23 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
232 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
75 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
33 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
24 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
32 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
83 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
66 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
81 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 ...
1
vote
1answer
449 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 ...
1
vote
1answer
371 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 ...
2
votes
1answer
77 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
4k 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
448 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
22 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
108 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
36 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
694 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
137 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
136 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
405 views
Select youngest record
I have a MariaDB table "mdata" with the following schema:
+-----+-----------+---------------------+------+
| id | room | date | temp |
+------+-----------+---------------------...
2
votes
1answer
107 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 ...
