Questions tagged [datetime]

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

Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
7