All Questions
6,350 questions
0
votes
1
answer
30
views
MySQL: get daily count of items which have a start_date and an end_date
I do have tours in a table which do have a start date and an end date, e.g.
id | start_date | end_date
1 2025-06-13 2025-06-13
2 2025-06-12 2025-06-17
3 2025-06-20 2025-06-21
4 ...
- 83
1
vote
0
answers
23
views
When a column has multiple values per user how do I select the most recent one according to date in another column? [duplicate]
Below is the query which works fine however Status column can have several values per user and I only want the row with the most recent date. This is in another column called modified in the ...
- 111
0
votes
2
answers
31
views
How to turn everything from a per-year basis to a per-school-year basis? [duplicate]
We have a lot of data with a lot of events with a lot of dates in a MySQL db. Generating lists of e.g. events per year, or students per year, is fairly simple. Now, we've decided to change everything ...
- 417
1
vote
1
answer
72
views
mysql Duplicate row 50 times but change the date recurring interval
Hi I have a table called work_events ( id, date_time, title).
It has 1 row: (1,2024-09-07 11:00, GroupMeeting)
No, i want to copy that row like 51 times. the only thing is that the date on each row ...
- 11
3
votes
2
answers
50
views
Index not hit in mysql
When I select all columns, the indexing does not work, but if I select just a few columns, it does.
mysql> explain SELECT * FROM fatura USE INDEX (datapago_serv_pago) WHERE id_servidor = 10 ...
- 33
0
votes
1
answer
69
views
SQL query to select data between two dates not returning any record
I am trying to get the records of the transaction between two different dates for example
sales record for the period of 2024-07-01 to 2024-08-20 from javafx date picker using the following query
...
1
vote
1
answer
46
views
MySQL: Date String to Date to Month coming back as Null
The dates in this database are in the form of a string, and in this format:
"1/5/2019"
I can use this query to convert the string dates to a date:
SELECT STR_TO_DATE(`date`, '%m/%d/%Y') ...
- 3,043
0
votes
0
answers
35
views
MySQL Date Column Index (NO Datetime)
I have a table with a date column (NO datetime, date only), which I always use to retrieve information by YEAR using BETWEEN. I was wondering if it's more efficient (and faster) to add a column to ...
- 79
0
votes
1
answer
225
views
MySQL Error Code: 1411. Incorrect datetime value: 'date' for function str_to_date
I'm attempting to change the date format from MM/DD/YYYY to YYYY-MM-DD.
Code:
SELECT `date`,
STR_TO_DATE(`date`, '%m/%d/%Y')
FROM example_table;
Results(success):
date ---> STR_TO_DATE(`date`,...
1
vote
1
answer
32
views
Add days to date without using adddate
Add a integer number of days to a date in MySQL without using adddate.
Obviously the following code does the job:
adddate('2024-01-01',100)
My failure attempt is:
'2024-01-01' + 100
Does anyone know ...
0
votes
1
answer
50
views
MySQL Query to fill a gap of data
I'm managing a DB which had some problems and I was asked to fill the gap where the database wasn't reading with previous data which was already there. So, the table sensors doesn't have data between ...
0
votes
1
answer
54
views
Changing data from DD/MM/YYYY to mySQL format YYYY-MM-DD [closed]
I am currently trying to get the data that I loaded from a .txt file to show the data in a way that I can manipulate it via excel's YEAR(function). The raw data date format looks like this:
01/03/2018
...
- 3
0
votes
0
answers
33
views
how to count days elpased between each interval using mySQL?
I have a table that has date range and i want to calculate the days that has been elapsed between 2 date
workorder
sequence
start date
end date
123
1
2024-01-02
2024-01-05
123
2
2024-02-01
2024-02-...
- 1
1
vote
1
answer
51
views
MySQL question. Struggling to convert text string in correct date format
I have a column named strdate in MySQL database with a table named data. The strdate column is of type varchar(100)
I tried a couple of ways to use the select command to get results in a legitimate ...
-1
votes
1
answer
46
views
mySQL: SUM electrical usage data by peak use, off peak use and total use GROUP BY site and day
I have a mySQL table with 5 or 15 minute interval electrical usage data. I would like to sum each site's yesterday usage and summarise 'off peak', 'peak' and Total use for each site.
Desired Output:
...
- 5
0
votes
1
answer
74
views
Check if date exists MySQL
Im validating an csv file that is being imported to the database via LOAD DATA LOCAL INFILE and im using a before insert trigger to achieve that (it is a dirty table, so when this process finishes i ...
- 103
-2
votes
1
answer
80
views
finding out the train station based on train arrival and departure in MySQL
I have to find out the train station in which the trains have arrived in the present day and have departed the next day, For example a train arrived at 11:55 PM but departed at 12:10 AM. I have to ...
- 1
-1
votes
2
answers
67
views
How would I find records in the future
If a slot is recurring it happens at the same time each week.
How would I fetch records scheduled later in the day, week, or month, considering the current date and time? I am using MySQL.
My current ...
0
votes
1
answer
207
views
Using DATE as part of a composite foreign key in MySQL
I have a table that, minimally, has
CREATE TABLE IF NOT EXISTS T (
A int(11) NOT NULL,
B date NOT NULL
);
A and B themselves happen to have FK constraints later on, but since this isn't part ...
- 57
0
votes
1
answer
62
views
How do i order by month when using the FORMAT function
I tried to do it but it keeps messing up the order for some reason. I tried DATE_FORMAT and it says it's not a recognized built-in function name
Select
FORMAT(ProcessDate, 'MMM-yy') as ProcessDate,
...
1
vote
1
answer
368
views
Datetime field overflow error while inserting
I have a table test_a
CREATE TABLE `test_a` (
`CUSTOMER_RK` int DEFAULT NULL,
`CUSTOMER_STATUS` varchar(45) DEFAULT NULL,
`EFFECTIVE_FROM_DTTM` date DEFAULT NULL,
`...
1
vote
1
answer
85
views
Getting dates that exist in one table but not the other table using MYSQL and VB
I know there are many answers to this very question on the forum, but I simply cannot figure out what I am doing incorrect. I've tried many methods and some got me close but not all the way there.
I ...
- 11
0
votes
1
answer
79
views
Search between dates with exclusion
I have completed Part 1:
I have a table called Clients and a column in it called Last_Seen. I need to go back 6 Months and forward one week (Search between 180 and 173 days back) and grab those ...
- 17
1
vote
1
answer
63
views
Query MySQL PHP 6 Months in past and filter/query those results
I need to go back 6 Months and forward one week. Grab those records that were seen during that one week window and nothing after that one week window. I got two of the three steps completed using this:...
- 17
0
votes
0
answers
43
views
What edge/corner case am I missing with this MySQL query?
This leetcode sql question was giving me problems earlier today. The question is as follows:
Table: Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id ...
- 43
0
votes
0
answers
46
views
Adding column usign extract(year_month) in result query
I have a doubt when usign extract(year_month).
Follow below my query:
SELECT products.sku, SUM(sells_products.quantity) AS quantity, EXTRACT(YEAR_MONTH FROM sells.date) AS period FROM sells, ...
-2
votes
1
answer
40
views
JOIN and GROUP BY DATES but missing empty rows
Maybe I'm tired, but can someone please explain to me why I'm not getting the empty rows?
I want this:
230901
id from join
id from join
id from join
230902
no rows matches in table B
230903
id from ...
- 39
2
votes
1
answer
69
views
Transform a table based on date overlap
Problem statement
Hi there, I'm a beginner SQL user. I have an access to a database for research, there's one table of interest structured as follow:
id
date
use
item
1
2023-02-01
90
A
1
2023-03-01
...
- 25
0
votes
1
answer
50
views
How i can get the current date from java android application and store it in mysql DB if phone language arabic or english
In my Android app I need to get the current date and insert it into MySQL DB. The issue is that when the phone language is English, everything is OK, but if it is in Arabic, the date is inserted as ...
0
votes
1
answer
115
views
Insert datetime into mysql java spring mvc
I save the column gioBatDau in MySQL as datetime, in POJO as
@Column(name = "GioBatDau")
@Temporal(TemporalType.TIMESTAMP)
@DateTimeFormat(pattern = "yyyy-MM-dd'T'HH:mm:ss")
...
-1
votes
1
answer
51
views
Why does subtracting NOW with DATE column give nonsense numbers?
Hello everyone,
i have a problem with the subtracting a DATETIME column with todays DATETIME.
One of the columns has the HireDate (Date of hiring a certain employee). I want to know how many Years ...
- 21
0
votes
2
answers
60
views
How to query from mutiple tables and group them by DATE in laravel?
I want to get data from two tables and group them by date, like a daily report that shows data from two tables.
Example output:
Date
Active User
Total Deposit
2023-07-31
10
12500
2023-08-01
5
10000
...
- 23
-1
votes
1
answer
694
views
Change default value to a null for date column in mysql
How to change the default value of a date column to a null ? Not able to add a new column to a mysql table because of the default property of the date column.
Table name is task_value;
Date column is ...
-1
votes
2
answers
121
views
SQL query to compare the average of two different sql queries with dates
I have a table with name_id, date and col1-col5 with rates.
I'm looking for a solution to compare the average of the rates of the latest day with the average of the rates of the day before the latest ...
- 34
0
votes
0
answers
18
views
Why won't my data push to the table using prepared statement? [duplicate]
I have a table that I am trying to INSERT INTO three different columns (child1Name, dobChild1, ageChild1) using a prepared statement. I can insert the data into this table within phpmyadmin utilizing ...
-1
votes
1
answer
222
views
Get a percentage of users from the past 30 days from the current date
I am on laravel mysql and I want a percentage and number of how many new users have been created or left in the past 30 days.
For example, if there are 3 users before 30 days and 2 new users have been ...
- 151
-2
votes
3
answers
32
views
MYSQL get max. values from a specific date
I have a table with multi-year data of temperature measurement.
Looking for the highest measured temperatures including the date of recording from the current year only.
Tried following:
SELECT ...
- 25
0
votes
1
answer
52
views
MySQL Minimum/Maximum Supported DATE
MySQL claims:
MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
Therefore, I expect the following query to result in (NULL, NULL).
...
- 21.1k
0
votes
1
answer
24
views
mysql update column with value from another table with a different date format
I need to replace in the MySQL tables of a WooCommerce the value of the wp_posts.post_date_gmt field by the value of a meta field _date.
I was thinking of doing something like this (untested):
UPDATE ...
- 35
0
votes
1
answer
39
views
Aggregate rows only if dates range overlap
I am trying to build a complex sql query for hours but still didn't find any way to do it as expected.
Here is my table and my dataset :
create table Skills
(
ID varchar(10),
StartDate date,
EndDate ...
- 1,300
0
votes
0
answers
16
views
Mysql LODA DATA INFILE statement fails to format dates [duplicate]
Mysql can read CSV files using LOAD DATA INFILE as long as the file is in its predefined location. This seems to work ok, except when it comes to dates. If I remove the dates, I can load the data. ...
user4966711
-1
votes
1
answer
116
views
Automatically update a MySQL table with the last 24 months
I have the following already created table in DBeaver:
Let's say that from this dataset in the ss On the 1st of August i want to add 2023-08-01 and delete 2021-07-01 from the 1st row.
I am using this ...
- 1
0
votes
1
answer
30
views
Sum data from different table in a week from bill
I can SUM data from different table for today, this month with this code.
For example SUM data for today:
include 'config_connect.php';
$today=date('d');
$thismonth=date('m');
$thisyear=date('Y');
$...
-1
votes
3
answers
53
views
SQL SELECT DATE Between
I started to SET my dates to (%d. %M %Y).
But now I cant find them with:
SELECT value FROM _data WHERE dates >= "01. October 2012" AND <= "01. December 2016"
The default ...
- 21
0
votes
0
answers
99
views
springboot switched one day back (-1) after reading from db (mysql)
I am developing application, which will have different timezone users data.
So I planned to store the date and datetime in UTC (MYSQL)
In application level I have set the timezone as UTC using below ...
1
vote
1
answer
11k
views
Generate 1 row for every month between a range of dates
I have the following data, as an example:
Name | Value | startdate | enddate
1 | 10 | 2023-04-01 | 2023-06-30
2 | 99 | 2023-03-01 | 2023-05-01
I need to get the ...
0
votes
2
answers
56
views
MySQL subtract bigint value as year
I have a MySQL database which holds one column years_valid_for as bigint and another column completion_date as datetime(6). I have to subtract bigint columns number as years.
e.g. years_valid_for is 4 ...
- 13
0
votes
3
answers
148
views
How to get the Max value else latest max value
DATE
ACCOUNT
VALUE
2023-06-06
1234
100
2023-06-07
1234
120
2023-06-08
1234
80
2023-06-06
3456
40
2023-06-07
3456
60
2023-06-08
3456
80
2023-06-05
5648
600
2023-06-06
5648
800
2023-06-06
5648
...
- 8,855
0
votes
2
answers
31
views
query all models that are not between two dates
I have a car model and an order model, i need a query that will filter the list of cars that are not rented between two dates, i'm lacking the logic to get the answer right what i came up with is this ...
- 23
0
votes
1
answer
311
views
How can I select data by date from a MySQL datetime column using Knex?
I want to select a data by the created_at that is a datetime column but I want to select it using only the date
In MySQL you can make it like this:
SELECT * FROM table WHERE date(`created_at`) = '2022-...
- 23