Is this a correct way to query a varchar date type in mysql?-CodePudding

I have table whose structure is describe table:

bundle  varchar(128)    NO  MUL     
deleted tinyint(4)  NO  PRI 0   
entity_id   int(10) unsigned    NO  PRI NULL    
revision_id int(10) unsigned    NO  MUL NULL    
langcode    varchar(32) NO  PRI     
delta   int(10) unsigned    NO  PRI NULL    
date_value  varchar(20) NO  MUL NULL

Date value are saved in the 2021-01-01 format. I am trying to query it for anything less than a particular date. For example:

select count(*) from table where table.date_value < '2021-01-05' order by table.date_value DES

This seems to be working as the range of records I am getting is within the correct range and the most recent (largest) date is 2021-01-04. but I hesitant to rely on this unless I know for sure it's a proper mechanism.

If this is correct, how does mysql do the comparison? Does it convert dates internally to timestamps?

CodePudding user response：

Ideally, the date_value column should be date or datetime type. Given that this is not a possibility for you, your current approach should be fine. This assumes that all text dates are stored in the format YYYY-mm-dd, which is an ISO format. If so, then your comparison against a string literal with the same format should work.