Question 114010 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to format dates as ordinal numbers in a text, in a postgres query?

I am looking to build a filter component where
my search comes like b_cycle_type = '1st Day Of The Month'

and in the database b_cycle_type is stored as -1,0,1,2,3,4,5

How can I prepare postgres statement

I am trying:

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

It’s not returning any results.

Tags:

2

Answers


  1. 0

    This part to_char(customers.b_cycle_type, '99th') actually results in ' 27th'(to_char Docs [*6th from the bottom]) so to combat this I would use the TRIM function.

    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'

    Example

    You can avoid the raw SQL but it’s not pretty

    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))
    Login or Signup to reply.
  2. 0 
    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');

    But:

    1. If b_cycle_type column is a date, you should define the column as a date type, not a numerical type. It will enable you to do simply where extract('day' from b_cycle_type) = 27. It’ll also take care of validating all data anyone tries to insert into the table, without having to maintain custom triggers and constraints.
    2. If for whatever reason you have to have this as a day offset, you should make it a smallint or even decimal(2,0). Also, save the actual date it corresponds to, as a separate column, in order to be able to easily account for months with different lengths, as well as leap years when February is longer.
    3. If you can’t alter the "customers" table structure, whenever you deal with the data from the table, you should make sure b_cycle_type is between -1 and 31, and within possible number of days for a given month in a given year.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search