I'm using to_char to get the day from the timestamp in postgres. On converting the result of that timestamp to varchar, because I want to apply some filters of type varchar, it doesn't give the correct result.
This query -
select * from (SELECT to_char(date (tableA.date), 'Day') AS day from tableA) a ;
gives weekdays in day column, which are days.
This query -
select * from (SELECT pg_typeof( to_char(date (tableA.date), 'Day')) AS day from tableA) a ;
gives text in day column. because the type of it is "text".
And this final query gives 0 rows, it should give all the matching results.
select * from (SELECT to_char(date (table.date), 'Day')::VARCHAR AS day from table) a where day IN ('Thursday');
Actual Output is 0 rows, Expected Output is 10 rows.
CodePudding user response:
to_char(date, 'Day')
returns a padded version of the day name, e.g. 'Thursday '
instead of 'Thursday'
.
If you want to remove the padding you can either use the FM modifier: to_char(table.date, 'FMDay')
or trim: trim(to_char(date, 'Day'))
In general I would be very cautions about using locale specific comparisons e.g. on my computer this comparison would fail even if the padded spaces are removed, as it won't return Thursday
. It's better to use numeric values, e.g. extract(isodow from date) = 5