snippetMinor
How to get the data of a recent year
Viewed 0 times
therecentyeargethowdata
Problem
I have a table in a MariaDB database.
The structure of this table is as follows
for example, the data in this table is like this:
I need to get data where the year column is greater than 2023 and the month column is greater than 1. That is, the data whose date is after 01/01/2023, but I don't have a date column and I can only work with the year and month columns. to better understand the issue for example suppose today is
The structure of this table is as follows
CREATE TABLE `EntityMonthlyQuantities` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`EntityType` varchar(400) NOT NULL,
`EntityGuid` uuid NOT NULL,
`Year` int(11) NOT NULL,
`Month` int(11) NOT NULL,
`Quantity` decimal(20,3) NOT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_EntityMonthlyQuantities_Unique_EntityGuid_And_EntityType_And` (`EntityGuid`,`EntityType`,`Month`,`Year`) USING HASH,
KEY `IX_EntityMonthlyQuantities_EntityGuid` (`EntityGuid`),
CONSTRAINT `Ck_EntityMonthlyQuantities_NonEmptyEntityType` CHECK (`EntityType` <> ''),
CONSTRAINT `Ck_EntityMonthlyQuantities_LowerCaseEntityType` CHECK (cast(`EntityType` as char charset binary) = lcase(`EntityType`)),
CONSTRAINT `Ck_EntityMonthlyQuantities_NonEmptyEntityGuid` CHECK (`EntityGuid` <> '00000000-0000-0000-0000-000000000000')
) ENGINE=InnoDB AUTO_INCREMENT=467 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_cifor example, the data in this table is like this:
I need to get data where the year column is greater than 2023 and the month column is greater than 1. That is, the data whose date is after 01/01/2023, but I don't have a date column and I can only work with the year and month columns. to better understand the issue for example suppose today is
13/01/2024 and one year ago it will be 13/01/2023. I want exactly the data that exists in the database between these two datesSolution
That is, the data whose date is after 01/01/2023, but I don't have a date column and I can only work with the year and month columns.
No problem, just filter on them like the integer columns they are like so:
No problem, just filter on them like the integer columns they are like so:
SELECT *
FROM EntityMonthlyQuantities
WHERE (`Year` = 2023 AND `Month` > 1) -- Anything after the first month of 2023
OR `Year` > 2023 -- Or anything after the year 2023 tooCode Snippets
SELECT *
FROM EntityMonthlyQuantities
WHERE (`Year` = 2023 AND `Month` > 1) -- Anything after the first month of 2023
OR `Year` > 2023 -- Or anything after the year 2023 tooContext
StackExchange Database Administrators Q#334502, answer score: 4
Revisions (0)
No revisions yet.