4

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:

enter image description here

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

8
  • 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
    Commented Jan 3 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.
    Commented Jan 3 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
    Commented Jan 3 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.
    Commented Jan 3 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
    Commented Jan 4 at 1:00

3 Answers 3

4

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

5
  • I'm not super familiar with this syntax, but would this still include rows with Month = 1 and Year = 2024 for example?
    – J.D.
    Commented Jan 3 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
    Commented Jan 3 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.
    Commented Jan 3 at 17:48
  • Nice - just tried it out here: dbfiddle.uk/8lNXS6Yx
    – JohnLBevan
    Commented Jan 3 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
    Commented Jan 4 at 0:58
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.