如何使用字符串过滤Django中的DateField或DateTimeField?

Fair enough, "Mar 09" is not a valid date – but the raw SQL is valid and runs ( I tested this with '2016-03-09' to bypass this error and get django to create the SQL)SELECT "appts"."appt_id", "appts"."client_id", "appts"."therapist_id", "appts"."agency_id", "appts"."appt_date", "appts"."appt_start", "appts"."appt_end", "appts"."cpt_code", "appts"."client_owe", "appts"."insur1_owe", "appts"."insur2_owe", "appts"."note", "appts"."note_flag", "appts"."ct_fee_pd", "appts"."subm_date", "appts"."updated" FROM "appts" WHERE to_char("appts"."appt_date",'Mon DD, YYYY') ~* 'Mar 09'我可以使用 extra()，但这已被弃用.I could use extra() but this will be deprecated. 我可能会使用 SO算法(使用原始sql只是为了获得此功能而无法妥协)，但是我将失去一些我想要的功能.I am probably going to wind up parsing the string using this SO algorithm (using raw sql is too large of a compromise just to gain this functionality), but I will lose some of the functionality that I want .不确定这是否可行... Not sure if this is doable...推荐答案 解决方案1 ​​ 创建一个构建Q对象的方法来测试零件中的日期:Create a method that builds Q objects to test the date in parts:def _filter_by_date_field(self, fld_value, searchableColumn, outputQ): mon_val = -1 Mons = ['jan','feb','mar','apr','may','jun','jul','aug','sep','oct','nov','dec'] if len(fld_value) >= 3 and fld_value[0:3].lower() in Mons: mon_val = next(i for i,v in enumerate(Mons) \ if v == fld_value[0:3].lower())+1 outputQ |= Q(**{searchableColumn+"__month" : mon_val}) if len(fld_value) >= 6 and mon_val > -1 and \ re.search(r'^\d{2}$', fld_value[4:6]): outputQ &= Q(**{searchableColumn+"__day" : fld_value[4:6]}) if len(fld_value) >= 8 and mon_val > -1 and \ re.search(r'^\d{4}$', fld_value[8:12]): outputQ &= Q(**{searchableColumn+"__year" : fld_value[8:12]}) return outputQ如果该字段是DateField，则该方法的调用方式如下:If the field is a DateField, the method is called like this: if isinstance(<ModelName>.model._meta.get_field(searchableColumn), DateField): outputQ |= self._filter_by_date_field(customSearch,\ searchableColumn,outputQ)下面是一个完整日期的SQL Django构建示例:Here is an example of the SQL Django builds for a complete date:EXTRACT('month' FROM "get_appts_vw"."appt_date") = 3) AND EXTRACT('day' FROM "get_appts_vw"."appt_date") = 5 AND "get_appts_vw"."appt_date" BETWEEN '2016-01-01'::date AND '2016-12-31'::date) 解决方案2 使用CharField为模型创建数据库视图(原始模型使用DateField)create a database view for models using CharField (where the original model used a DateField)class ApptView(models.Model): appt_date = models.CharField(max_length=12) ….class Meta: db_table = u'get_appts_vw' # view name视图 CREATE VIEW get_appts_vw AS select to_char(a.appt_date,'Mon DD, YYYY') as appt_date …. from appts这可行，但仍然需要额外的()子句进行排序This works but still requires an extra() clause to do sorts 解决方案3 (我正在使用它)from django.db.models import Lookupfrom django.db.models.fields import DateFieldfrom django.db.models import CharField, [email protected]_lookupclass TextDate(Transform): lookup_name = 'txtdt' @property def output_field(self): return CharField()@TextDate.register_lookupclass DateTextFilter(Lookup): lookup_name = 'dttxt' def as_postgresql(self, compiler, connection): lhs, lhs_params = compiler.compile(self.lhs.lhs) rhs, rhs_params = self.process_rhs(compiler, connection) params = lhs_params + rhs_params return 'to_char(%s,\'Mon DD, YYYY\') ~* %s ' % (lhs, rhs), params这样叫from .CustomFilters import * # or whatever you name your custom filters module.... if isinstance(<ModelName>.model._meta.get_field(searchableColumn), DateField): outputQ |= Q(**{<column_name>+"__txtdt__dttxt" : <search_value>}) 这篇关于如何使用字符串过滤Django中的DateField或DateTimeField?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！