James G Wilkinson <jgw@alpinegeophysics.com> writes:
> beta_jgw=# update scenario1.time_test set local_day = extract(day from
> to_timestamp(to_char(gmt_date,'YYYY-MM-DD')||'
> '||to_char(gmt_hour,'99')||':00:00-00','YYYY-MM-DD HH24:MI:SS') at time
> zone 'EST');
That seems like a mighty ugly way to do it; have you considered some
kind of timestamp arithmetic instead? But anyway, I suspect your
problem is you need a few 'FM' format specs to keep the to_char
functions from introducing unwanted spaces. Look at the intermediate
results of your expressions.
regards, tom lane