CODEWITHSUNDEEP
postgresql
Hristian Yordanov
Hristian Yordanov

Reputation: 668

Leading Zero when extract Day and Month

Hello i'm trying to extract current day and month in a query. This query is filling table from csv file. the file is named like this:

LOG_01_01_2018.csv

My query search for file:

LOG_1_1_2018.csv

With no Zero in front day and month. How to add Zero numbers?

Here is the code:

 execute format ($f$COPY tmp_x FROM 'D:\Programs\PS\download_files_from_ftp_avtomat\files\LOG_%s_%s_%s.csv' 
(header, FORMAT CSV, DELIMITER ',', NULL ' ', ENCODING 'WIN1251');
$f$,extract(day from now()),extract(month from now()),extract(year from now()));

Upvotes: 12

Views: 21681

Answers (3)

aurel_nc
aurel_nc

Reputation: 153

Looks shorter to use to_char():

SELECT to_char(now(),'DD')||'/'||to_char(now(),'MM')||'/'||to_char(now(),'YYYY')

Upvotes: 3

Vao Tsun
Vao Tsun

Reputation: 51609

https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

extract(field from timestamp) returns double precision, so you either lpad zero to it (making the value text), or just use to_char with data mask, eg:

t=# select extract(day from now()), to_char(now(),'MM'),extract(year from now());
 date_part | to_char | date_part
-----------+---------+-----------
        19 | 01      |      2018
(1 row)

Upvotes: 5

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522234

One option uses LPAD:

execute format ($f$COPY tmp_x FROM 'D:\Programs\PS\download_files_from_ftp_avtomat\files\LOG_%s_%s_%s.csv'
(header, FORMAT CSV, DELIMITER ',', NULL ' ', ENCODING 'WIN1251');
$f$,
    lpad(extract(day from now())::text, 2, '0'),
    lpad(extract(month from now())::text, 2, '0'),
    extract(year from now()));

The year would always be a fixed width four digit number, unless you plan to work with data which existed before computers were around.

Upvotes: 20

Related Questions