We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [datetime]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
32 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). ...
6
votes
4answers
3k views

How do I find the next saturday in MySQL

I have trigger and a stored procedure in it (so SP runs when trigger runs). I need a function which finds the next saturday to put in SP. So lets say, it is Wednesday (2015-7-22) today. If my ...
0
votes
1answer
301 views

Calculate Total Working Minutes in MySql

I have a design a table for attendance and here are the genral fileds AttendanceMachineLoginId EmpId AttendanceDateTime Whenever user will come to office, he has to make his attendance. The first ...
0
votes
2answers
40 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
25 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
50 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, ...
-1
votes
2answers
20k views

How to extract data between yesterday and today?

I have a column called createdate of datetime datatype. I want to extract data between today and yesterday. select * from table1(nolock) where createdate ='2018-06-01' --not getting any output I ...
2
votes
1answer
28 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
46 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
1answer
290 views

How to pass, specific day month and year in the below query?

I have registration according to the data in a table and I want to show the whole month, I found the easy solution with below query but I do not know how can I pass specific day, month and year in ...
5
votes
3answers
6k views

MySQL - Merge or split datetime intervals (start date to end date)

I have a table which stores a list of activities with an interval of time delimited by 2 dates. Sample: +------+---------------------+---------------------+-------------+ | name | start ...
0
votes
1answer
388 views

Select different dates from mysql

I have a database structure like this ID | title | startTime | endTime And what I need is to get 3 records from the database where the first recorder is following this structure DAY(NOW())-1 the ...
0
votes
2answers
30 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: ...
1
vote
1answer
778 views

Weed out certain rows where timestamp is within interval

Given a table structure: create table contacts ( contact_id int(8) not null auto increment, #primary key pid int(8) not null, #personal id contakt_time datetime, ...
2
votes
2answers
1k 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 ...
1
vote
3answers
3k views

MySQL - query to get length of service in Years, months, days

I have the following query that I'm trying to get the length of service of a person at their company. The idea is to find how many years, months, and days they have served there. For example: ...
0
votes
1answer
110 views

Finding Count of Occurrences & Time Gap between a Timeline in MySQL

I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following: How many times there has a been a flip-flops (for YES ...
1
vote
1answer
200 views

how to ignore DST in MySQL

We have a database with flights, and in there, departureTime DATETIME field. Extra info: global.time_zone: Europe/Dublin session.time_zone: Europe/Dublin MySQL version: 5.7 Problem is, I need to ...
0
votes
3answers
58 views

Select youngest record

I have a MariaDB table "mdata" with the following schema: +-----+-----------+---------------------+------+ | id | room | date | temp | +------+-----------+---------------------...
0
votes
1answer
1k views

Phpmyadmin feed a timestamp column with “0000-00-00 00:00:00”?

in PhpMyAdmin (OVH Mutu), a table with column type timestamp. Impossible to put into this colum a timestamp : Always display datetime like 2017-01-09 23:02:01 !!! It generate several problmes : ...
0
votes
1answer
269 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 ...
2
votes
1answer
35 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
30 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 ...
4
votes
1answer
249 views

What does 0 in DATEDIFF(MINUTE, 0, <Date>) actually mean?

So, our data team asked for some help in solving a problem they had. I eventually tracked it down to some really out of range data (1/1/0001) and a DATEDIFF function they were using. While I've ...
15
votes
5answers
5k 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 ...
1
vote
2answers
837 views

MySQL to SQL Server - Unixtime being major problem

I'm trying to convert this query to SQL Server. It already has a lot of updates. Still, I don't know what to do from here ( i'm not the best when it comes to programming in MySQL ) SELECT wo....
0
votes
0answers
19 views

Redshift/Athena: Most efficient way to cast timestamps of varying precision to date in query?

I'm receiving a stream of log files to an S3 bucket and querying this data through Amazon Athena (Redshift). Contained in the log files are timestamps with the seconds field having decimals to the ...
11
votes
2answers
66k views

Convert a datetime column to number of seconds

In my SQL Server database, I have a datetime column. What is a good way to create a new column that represents the long value for the datetime column? The long would represent a number of seconds. I ...
6
votes
1answer
450 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: ...
0
votes
1answer
53 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 ...
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
18 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 | +----+---------+------------+---...
1
vote
2answers
1k views

Find the OverLapped (Common) Date Range from Group of Date Ranges

How can I find the Overlapped (common date range) from a group of Date Ranges given? The following table contains events and programmes (EID and PID respectively) CREATE TABLE #EventsTBL ( PID ...
0
votes
0answers
24 views

Time wise user online count

I'm not a DB expert but i'm trying to get time wise user online count to show in a graph. I have manage to get online user count for current time but how to get for a time span ? i need each minute ...
0
votes
0answers
21 views

Selecting time_Stamp >= time('now') GROUP BY firstName RETURNS all first names and same time?

I am trying to select two columns from two different tables, where a value exists in both tables and the time in one table is >= now - 30 minutes. How ever it is returning all the firstnames and then ...
4
votes
1answer
534 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
41 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
85 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, ...
1
vote
1answer
157 views

Index Optimization for Datetime comparing day of week and hour

I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int, and to fill in gaps with data we have models by day of week and hour of day: model_id int, hour_of_day int, ...
1
vote
1answer
59 views

Subtracting Days From Current Date

I know this is probably very basic, but I'm trying to create a calculated field that subtracts X number of from Current Date, based off of the value of another field: CASE WHEN DaysPD = 30 THEN 1 ...
0
votes
1answer
1k views

CONVERT to datetime fails on Dynamic SQL - “Conversion failed when converting date and/or time from character string” [closed]

I've spent a few hours with this and still can't get it to work. I'm trying to get the path to the most recent file that matches a pattern for a dynamic SQL query where I'm loading a CSV file into a ...
1
vote
1answer
34 views

calculate days spent of all visitors from encounters

I have an encounters table CREATE TABLE visitors( id serial PRIMARY KEY, start_date TIMESTAMP NOT NULL, -- visitor_id bigint NOT NULL -- skip for now, end_date TIMESTAMP ); (end_date can be ...
0
votes
3answers
2k views

How do we get an hour, minute and second difference between two datetimes?

The table Task has columns as: Task(TaskID, TaskName, CreatedDate) And want to execute code block based on datetime difference as: IF Task CreatedDate has passed 15 Minute THEN Execute ...
1
vote
1answer
50 views

SQL Server 2008 onward - Globalization with Date Time

My application is standalone windows application. Database is SQL Server 2008 onward. The application will be deployed on systems around the globe. User should view the date/time values in their own ...
4
votes
1answer
1k views

MySQL calculate overlapping period excluding overlapping rules

I would like to calculate the period overlap in hours based on special rules. I created a test setup, that works as long as the rules themselves don't overlap. Example non overlapping rules: id ...
1
vote
1answer
104 views

Get closest previous date

I need to get the posts closest to a date. SELECT id , datetime FROM dates WHERE id != 150003 AND datetime > FROM_UNIXTIME(1549622615) ORDER BY datetime LIMIT 1 Returns a correct ...
1
vote
1answer
30 views

redesign database around selecting rows by time range

I currently have a MySQL database that grows about 50,000 rows a day. When querying data out of the database, I query information from the info table and use a subquery on the session table to refine ...
11
votes
2answers
5k views

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
4
votes
4answers
12k views

How to get Time in milliseconds & How to add 5 minutes to the milliseconds in mySql

How to get current time in milliseconds & how to add 5 minutes to it in mySql
0
votes
1answer
54 views

Converting MySQL 5.5 datetime binary to MySQL 5.6 (math problem)

I received a database "backup" of binary files (.frm and .ibd) that were copied from a MySQL 5.5 install. Unfortunately, I was unable to get these files to import into a MySQL 5.5 vm that I created. ...