Questions tagged [datetime]

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

Filter by
Sorted by
Tagged with
5
votes
2answers
181 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 ...
0
votes
0answers
7 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
1answer
43 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 ...
0
votes
2answers
52 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' '...
14
votes
1answer
746 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
votes
1answer
29 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 ...
6
votes
1answer
597 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 ...
0
votes
1answer
22 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 ...
1
vote
1answer
240 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 ',' ...
0
votes
1answer
82 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 ...
4
votes
1answer
155 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 ...
0
votes
1answer
41 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
votes
1answer
29 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 ...
-2
votes
1answer
27 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 ...
0
votes
0answers
25 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 ...
1
vote
1answer
17 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: ...
6
votes
1answer
693 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 ...
0
votes
1answer
129 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
88 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
68 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
95 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 ...
3
votes
1answer
2k 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
79 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 | +-------...
12
votes
4answers
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 ...
1
vote
2answers
791 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
82 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
224 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
33 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
vote
2answers
494 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
330 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
84 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
588 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
47 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 ...
5
votes
1answer
357 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
30 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
227 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
57 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
521 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
25 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
239 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
469 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
24 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
35 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
38 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
84 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
160 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
103 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
140 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 ...
2
votes
1answer
818 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
44 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
2 3 4 5
7