Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove blank-padding from to_char() output

I generate a view from this:

create or replace view datetoday as
select to_char(dt, 'yyyy-mm-dd') as date, to_char(dt, 'Day') as weekday from 
(select ('2013-03-01'::date + i) dt from generate_series(0,'2013-03-03'::date - 2013-03-01'::date) 
as t(i)) as t;

It gives me the weekday info as text type. Then I use:

select date::date, weekday::varchar from datetoday;

Now the table is like

2013-3-1 Friday
2013-3-2 Saturday

If I want to choose the entry:

select * from datetoday where weekday='Friday'

to change it from text to character varying.

It seems that the length is fixed is not according to each word length.
For example 'Friday' should have length 6 and Wednesday length 9.
How can I change this, let the length be the actual length of the word?

Because later I will compare this table's weekday column to another table's weekday column. Like

where a.weekday=b.weekday

The other weekday is from user from jsp, so the length varies.
Now the length is fixed, the comparison fails.

like image 381
user3382017 Avatar asked Mar 07 '14 05:03

user3382017


1 Answers

The pattern 'Day' is blank-padded to the right, making all days 9 characters long. Use the FM Template Pattern Modifier to remove any padding:

SELECT d::date AS day
     , to_char(d, 'yyyy-mm-dd') AS day_text
     , to_char(d, 'FMDay')      AS weekday
FROM generate_series('2013-03-01'::date
                   , '2013-03-07'::date
                   , interval '1 day') d;

Also demonstrating generate_series() for timestamps. One less query level.
If you need an actual date in the view, make it an actual type date, don't convert to text and back.
And don't use the basic type name date as column name. Using day instead.
And I would just use text for the text. No point in converting to varchar.

like image 106
Erwin Brandstetter Avatar answered Sep 27 '22 23:09

Erwin Brandstetter