All Questions
23 questions
0
votes
0
answers
95
views
Keep both non-overlapping intervals and overlapping ones with a larger id MySQL
I have the following table in MySQL: data(interval_id, object_id, start, end), where interval_id, object_id are integers and interval_id is unique per every object_id, while object_id itself is unique....
- 101
0
votes
2
answers
60
views
How I do I aggregate or perform calculations on a time datatype in "MM:SS:MS" in MySQL?
I'm currently working on an NBA project and I need to take the average minutes played per player, but I have no idea how to aggregate or calculate the time datatype.
I know you have to convert it, but ...
6
votes
3
answers
1k
views
Store time series data efficiently without wasting space
I'm building an application to calculate different KPI metrics for customers of an ecommerce website e.g. (avg. order value, avg. items count and so on). KPIs are integer and or double values e.g. ...
- 63
2
votes
1
answer
1k
views
Same query on same same table, different response time
I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes.
The setup is:
* virtual server;
* 4 virtual CPU;
* 8 GB of virtual memory ;
* ...
- 21
3
votes
1
answer
46
views
Lock database in time
I'm currently working with prototyping some stuff with a dump of a MySQL (5.7) database where much of the data is time dependent. Most of the queries I write use the NOW() function and the results ...
- 133
2
votes
1
answer
189
views
Sum values by time
id_ | date_ | hour_ | in_|out_|
|…. | …. | …. | …. | ….
|19 | 2018-12-12| 18:15:00| 1 | 3 |
|19 | 2018-12-12| 18:30:00| 1 | 0 |
|19 | 2018-12-12| 18:45:00| 2 | 4 |
|...
- 23
-2
votes
1
answer
827
views
Database design for a timesheet app?
I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user:
To enter for each day of the week the amount of time he ...
1
vote
1
answer
3k
views
Date and time period query
I would be very grateful if someone can help me here: in my database, I have prices recorded at a particular time in a day over years. So for each vendor or station, there can be several price data ...
- 13
0
votes
2
answers
231
views
How to add index to increase speed of mysql statement
How do I increase the speed of this mysql statement? taking 1.10s but was wondering if it could be made faster.
SELECT
res_rooms.room_id AS room,
res_rooms.type AS room_type,
...
- 75
2
votes
1
answer
764
views
Position of a race result using database query?
The scenario is based around a race event (running/jogging)
Is there a way of getting a position of a person by using their race results. For example the quickest person to finish a race would be in ...
- 73
1
vote
1
answer
817
views
How to reduce redundant time-series data in MySQL into a function?
i have a question that actually does not fit here very much, but maybe it fits. So, i have data like this:
How can i reduce this 3 entries with same values? Sure, i can just delete the other 2, but ...
- 11
1
vote
0
answers
287
views
Is there a faster way of importing 8m+ rows?
I'm creating a new feature on my website in order to provide player locations (my website is for a multiplayer gaming service), and am going to use a simple IP lookup in my database.
For this I need ...
- 111
2
votes
1
answer
535
views
Calculate hours based on a course schedule in mysql
I am trying to calculate user's attendance hours based on a course schedule.
The user enters a room and I'm tracking in & out time.
So I have the following tables
user_id | room_id | ...
- 21
5
votes
2
answers
4k
views
MySQL: sum time ranges exluding overlapping ones
I need to sum up the time which results from multiple time ranges. For example - we have enter / exit ranges of some office:
Query has to:
Exclude overlapping ranges (8:00 - 10:00)
Exclude "missing" ...
- 83
0
votes
1
answer
75
views
Time Required For Query Execution?
I am new to working with databases and am unaware of their capacity in general.
To be specific, I want to know much much time would a particular mysql query take. I tried searching on the internet but ...
4
votes
1
answer
1k
views
Any risks in updating the system time on a mysql 5.5 server?
We just discovered one of our mysql systems has not been running ntp, with the result that the time has drifted a bit.
If I enable ntp, and synchronize the time while the system is running are ...
- 1,232
0
votes
1
answer
851
views
Database design: handling time
I am developing a weekly timetable which ideally would allow teachers pick what subjects they teach. While most of this is fairly straightforward, from a conceptual point of view I am unsure how to ...
- 123
3
votes
1
answer
345
views
MySQL intersection on one table with time type
I've a table with 4 columns: id (int), name (varchar), timefrom (time), timeuntil (time)
Now I would like to have all combinations where they have an intersection on the timefrom to timeuntil.
My ...
- 33
0
votes
1
answer
447
views
Records in a table unique period
I have the following table:
+--+---------+-------+--------------+--------------+
|ID|HEADER_ID|VERSION|FROM_DATE_TIME| TO_DATE_TIME|
+--+---------+-------+--------------+--------------+
| 1| ...
- 235
2
votes
3
answers
11k
views
Can I use PHP function strtotime() in SQL queries?
I have this table, and I need to use a PHP function in the query.
id time lastTime
1 08:02
2 08:04
3 08:30
I am using this query with the strtotime(), and it's not working:
...
2
votes
2
answers
1k
views
Select different time
I looked on the internet and I found your post on the stackexchange.
I will try to explain in more detail what we really need
I do not know well to write English
It is a system for airporttaxi
id ...
3
votes
1
answer
1k
views
Risk of changing clocktime on MySQL server host
I have a MySQL server host that have a clock that is about 75 minutes wrong. Is there any risk in changing the hosts clock while MySQL is running?
Update: Obviously NOW() and other functions ...
- 197
2
votes
2
answers
4k
views
How to SELECT rows with time duration stored in varchar column?
There is a column in table defined like this :
duration varchar(10)
It has value for duration of a MP3 file stored in it like : 52:19 (Minutes : Seconds)
How can I create a SQL query, for ...
- 123