Convert WW Extract from SQLite to Postgres?-CodePudding

I have the following SQLite statement:

(strftime('%j', date( s.my_time, '-3 days', 'weekday 3'))) / 7 AS ww

How can I accomplish this same function on s.my_time in postgresql?

CodePudding user response：

In SQLite:

select (strftime('%j', 
date( '2022-09-03 21:03:44', '-3 days', 'weekday 3'))) / 7 AS ww;
34

In Postgres:

select extract(doy from '2022-08-31'::timestamp - '3 days'::interval)::integer/7;

 ?column? 
----------
       34

Though if you do:

select to_char('2022-09-03 21:03:44'::timestamp - '3 days'::interval, 'WW');
 to_char 
---------
 35

You get a different week as without converting the extract output to integer you get:

 select extract(doy from '2022-08-31'::timestamp)/7;
      ?column?       
---------------------
 34.7142857142857143