HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How to get the data of a recent year

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
therecentyeargethowdata

Problem

I have a table in a MariaDB database.
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_ci


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 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 dates

Solution

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:

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 too

Code 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 too

Context

StackExchange Database Administrators Q#334502, answer score: 4

Revisions (0)

No revisions yet.