如何在postgres查询中将日期格式化为文本中的序号

Question

我希望构建一个过滤器组件，其中我的搜索结果就像b_cycle_type = '1st Day Of The Month'

并且在数据库中b_cycle_type被存储为-1,0,1,2,3,4,5

如何准备postgres报表

我正在尝试：

SELECT "customers".* 
FROM   "customers" 
WHERE (CASE customers.b_cycle_type
WHEN -1 THEN 'Last day of the month'
WHEN  0 THEN 'Align with first'
ELSE to_char(customers.b_cycle_type, '99th') || ' Day Of The Month'
END = '27th Day Of The Month')

它没有返回任何结果。

Answer 1

这个部分to_char(customers.b_cycle_type, '99th')实际上产生了' 27th'(to_char Docs[*6th from bottom](，所以为了解决这个问题，我会使用TRIM功能。

CREATE TABLE customers(
id SERIAL PRIMARY KEY, 
b_cycle_type INT);
INSERT INTO customers(b_cycle_type)
SELECT * FROM generate_series(-1,30);
SELECT "customers".*
FROM "customers"
WHERE 
CASE b_cycle_type
WHEN -1 THEN 'Last day of the month'
WHEN 0 THEN 'Align with first'
ELSE
TRIM(to_char(b_cycle_type, '99th')) || ' Day Of The Month'
END = '27th Day Of The Month'

示例

你可以避免原始SQL，但它不是很好的

your_variable = '27th Day Of The Month'
customer_table = Customer.arel_table 
case_stmt = Arel::Case.new(customer_table[:b_cycle_type])
.when(-1).then(Arel.sql("'Last day of the month'"))
.when(0).then(Arel.sql("'Align with first'"))
.else(
Arel::Nodes::InfixOperation.new('||'
Arel::Nodes::NamedFunction.new('TRIM',[
Arel::Nodes::NamedFunction.new('to_char',
[customer_table[:b_cycle_type],Arel.sql("'99th'")])
]),
Arel.sql("' Day Of The Month'")
)
)
Customer.where(case_stmt.eq(your_variable))

Answer 2

SELECT "customers".* 
FROM "customers" 
WHERE (CASE b_cycle_type
WHEN -1 THEN 'Last day of the month'
WHEN 0 THEN 'Align with first'
ELSE 
CASE 
WHEN "customers".b_cycle_type BETWEEN -1 AND 31 
THEN trim(to_char("customers".b_cycle_type,'99th')||' Day Of The Month')
END
END = '27th Day Of The Month');

但是：

1. 如果b_cycle_type列是日期，则应将该列定义为date类型，而不是数字类型。它将使您能够简单地执行where extract('day' from b_cycle_type) = 27。它还将负责验证任何人试图插入表中的所有数据，而不必维护自定义触发器和约束
2. 无论出于何种原因，如果您必须将其作为日偏移量，则应将其设为smallint甚至decimal(2,0)。此外，将其对应的实际日期保存为单独的一列，以便能够轻松地计算不同长度的月份，以及二月较长的闰年
3. 如果无法更改"customers"表结构，则无论何时处理表中的数据，都应确保b_cycle_type在-1和31之间，并且在给定年份中给定月份的可能天数内