How to get the data of a recent year

Question

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

If you want from 01/01/2023 you want equal or grater than month 1. Also do you wish to include the data from 2024? — A-Tech, Jan 03 '24 at 16:00
@A-Tech Appreciate the thoughts on your edit to my answer, but OP said they want results after the first month of the year 2023, which is why I rejected the edit (which would've included the first month). — J.D., Jan 03 '24 at 16:18
That is, the data whose date is after 01/01/2023. This is impossible, none of your columns represent day-of-month. — MonkeyZeus, Jan 03 '24 at 21:02
@MonkeyZeus Obviously that's a linguistic mistake on OP's part, but it's not impossible either. The granularity of their data is to the Month and Year, so anything after 01/2023 (MM/YYYY) will implicitly be after 01/01/2023 (MM/DD/YYYY) too lol. — J.D., Jan 03 '24 at 22:00
If you want all the data for 2023 and none of 2024, then simply use WHERE year = 2023. I am downvoting because the title and the text do not agree, and it is unclear which one you want. — Rick James, Jan 04 '24 at 01:00
@RickJames Thanks, suppose today is 13/01/2024and one year ago it will be13/01/2023. I want exactly the data that exists in the database between these two dates. — Mohammad Miras, Jan 05 '24 at 04:13
@MohammadMiras - I strongly recommend that you store such in DATETIME (or TIMESTAMP), which expects "2024-01-31" format. See STR_TO_DATE() and DATE_FORMAT(). — Rick James, Jan 05 '24 at 04:51
@MohammadMiras: could you please include this comment into your post and or somehow settle on one requirement? The way it is now, you have three requirements that directly contradict each other. — Quassnoi, Jan 05 '24 at 20:29

score 4 · Answer 1 · answered Jan 03 '24 at 13:40

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

Quassnoi · Answer 2 · 2024-01-03T22:06:21.290

3

MariaDB supports the tuple syntax, so you can put your year and month into a single comparison condition:

SELECT  *
FROM    EntityMonthlyQuantities
WHERE   (year, month) > (2023, 1);

It will pull all the records with the combination of the year and the month lexicographically larger than (2023, 1) (i.e. everything strictly later than January 2023).

Note that these two statements in your post:

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

don't really play well together, because your aggregated data would probably include whatever happened between Jan 2, 2023 and Jan 31, 2023 under (2023, 1)

edited Jan 03 '24 at 22:06

answered Jan 03 '24 at 17:09

Quassnoi

631
3
6

I'm not super familiar with this syntax, but would this still include rows with Month = 1 and Year = 2024 for example? – J.D. Jan 03 '24 at 17:21
@J.D. of course, that's how lexicographic ordering works. This is doing exactly the same what your query is doing, only more concisely – Quassnoi Jan 03 '24 at 17:22
That's cool. Again, I'm not familiar with the syntax, which is why I asked. I'm familiar with lexicography but usually that's in regards to comparing a single element in a set to another single element. Here in a looser sense of the definition it seems you can compare tuples, which is neat. – J.D. Jan 03 '24 at 17:48
Nice - just tried it out here: https://dbfiddle.uk/8lNXS6Yx – JohnLBevan Jan 03 '24 at 20:56
Presumably all the Jan '23 values are lumped into one row. In that case, (year, month) > (2023, 1) wo;; start wotj Feb '23. Change the >' tp >=. – Rick James Jan 04 '24 at 00:58

score 0 · Answer 3 · answered Jan 05 '24 at 04:53

I strongly recommend that you store such in DATETIME (or TIMESTAMP), which expects "2024-01-31" format. See STR_TO_DATE() and DATE_FORMAT().

Once you have that, this would be a range of the beginning of Jan 31, 2023 through Jan 30, 2024:

WHERE mydatetime >= '2023-01-31'
  AND mydatetime  < '2023-01-31' + INTERVAL 1 YEAR

How to get the data of a recent year

3 Answers3