1

I am trying to convert an interval value to a string value using TO_CHAR() function. But I noticed when I pass formatter for weeks, it always returns at least 01, whereas it works as expected for days and months. For example:

postgres=# select to_char('2023-07-02'::timestamp - '2023-07-02'::timestamp, 'DD') as period;
 period
--------
 00
(1 row)

postgres=# select to_char('2023-07-02'::timestamp - '2023-07-02'::timestamp, 'WW') as period;
 period
--------
 01
(1 row)

postgres=# select to_char('2023-07-02'::timestamp - '2023-07-02'::timestamp, 'MM') as period;
 period
--------
 00
(1 row)

I even tried passing an interval like this:

postgres=# select to_char(interval '0 days', 'WW') as period;
 period
--------
 01
(1 row)

postgres=# select to_char(interval '0 days', 'DD') as period;
 period
--------
 00
(1 row)

postgres=# select to_char(interval '0 days', 'MM') as period;
 period
--------
 00
(1 row)

Please help. Is there any other way to convert an interval to 'weeks' string reliably?

DGulshan
  • 11
  • 3

1 Answers1

0

Just do it like this

select lpad(
    (abs('2023-07-02'::timestamp::date - 
         '2023-07-02'::timestamp::date)/7)::varchar
    , 2, '0') 
as period;

considering that

timestamp – timestamp returns interval and 
date – date returns integer (days)

and taking into account that is not possible extract weeks from intervals, just from time stamps because extract week returns the number of the ISO 8601 week-numbering week of the year, as can be seen in the official documentation

Pepe N O
  • 1,678
  • 1
  • 7
  • 11
  • The actual inputs are timestamps, that's why in my example I've explicitly casted `date` values to `timestamp` values. If I don't find any better alternative, I might just have to first extract the date part from timestamp values and then use what you suggested. Thanks. – DGulshan Jul 04 '23 at 17:28
  • 1) The `date` will automatically cast to `timestamp`: `select to_char('2023-07-02'::date, 'YYYY-MM-DD HH24:MI:SS'); 2023-07-02 00:00:00` 2) The question is dealing with an `interval` not a `timestamp`. – Adrian Klaver Jul 04 '23 at 17:36
  • Modified according to your comments. @DGulshan – Pepe N O Jul 04 '23 at 17:36