I need to manipulate column data in postgres

When I run the query-

SELECT t.date::varchar 
FROM   generate_series(timestamp '2020-02-27'
                     , timestamp '2020-03-01'
                     , interval  '1 day') AS t(date);

it returns -

2020-02-27 00:00:00
2020-02-28 00:00:00
2020-02-29 00:00:00
2020-03-01 00:00:00

I want -

20200227
20200228
20200229
20200301

Removed '-' and truncated from end. Can someone guide

CodePudding user response：

1. If you don't specifically need some features of a varchar, by default use text instead.
2. You don't need to cast every time - generate_series() will do that automatically once it detects your step is an interval. That's unless you specifically want the generate_series(timestamp,timestamp,inteval) variant, not generate_series(timestamptz,timestamptz,inteval).
3. If you cast to be explicit, cast your dates as date. Regardless of whether you leave it as text literal or make them actual dates, PostgreSQL will have to cast them to match the function definition.
4. If you're planning to group things by a text-based date or do that to truncate timestamps, consider date_bin() and date_trunc() as well as simply holding things as a native date type. It'll take less space, run faster and enable native date-specific functions.
5. Make sure you're using to_char() to its full potential - it can save a lot of formatting later.

SELECT to_char(t.date,'YYYYMMDD') as date
FROM   generate_series('2020-02-27'
                     , '2020-03-01'
                     , interval  '1 day') AS t(date);
--   date
------------
-- 20200227
-- 20200228
-- 20200229
-- 20200301