Postgresql - Unix time stamp from week of year-CodePudding

I would need to get the UNIX timestamp of a date and a week.

I solved the Date part like this:

EXTRACT(epoch FROM ('23/01/1980')::timestamp)

But cannot figure out how to do the same for the week of the year. For example:

EXTRACT(epoch FROM ('3-1980')::timestamp)

I need the timestamp of the first day of week 3 of the year 1980.

CodePudding user response：

Using the ISO-8601 definition of "week", you can use this somewhat brute force-ish method:

select min(val),extract(epoch from min(val))
from generate_series('1980-01-01'::timestamp,'1980-01-20'::timestamp,'1 day') dt(val)
where extract(week from val) = 3

Result:

| min                 | date_part |
 --------------------- ----------- 
| 1980-01-14 00:00:00 | 316656000 |

If your week values come from a table, you can extract the information and generate the appropriate value for each row:

select src.week_info as week_info
      ,min(list.dt)::date as first_day
      ,extract(epoch from min(list.dt)) as epoch
from (values ('3-1980'),('21-2012'),('47-2001')) src(week_info)
     cross join lateral (values (string_to_array(src.week_info,'-'))) arr(week_info)
     cross join lateral (values ((arr.week_info[2] || '-01-01')::timestamp,arr.week_info[1]::int)) cnv(start_date,week_num)
     cross join lateral generate_series(cnv.start_date   ((cnv.week_num - 2) || ' weeks')::interval
                                       ,cnv.start_date    (cnv.week_num      || ' weeks')::interval
                                       ,'1 day') list(dt)
where extract(week from list.dt) = cnv.week_num
group by src.week_info
order by min(list.dt)

Result:

| week_info | first_day  | epoch      |
 ----------- ------------ ------------ 
| 3-1980    | 1980-01-14 |  316656000 |
| 47-2001   | 2001-11-19 | 1006128000 |
| 21-2012   | 2012-05-21 | 1337558400 |

CodePudding user response：

Try concatenating year and week of the year and then convert it to date with to_date() using the pattern 'IYYYIW', e.g. to_date(concat(1980,3),'IYYYIW') to get the beginning of the 3rd week of 1980.

SELECT 
  to_date(concat(1980,3),'IYYYIW'),
  EXTRACT(EPOCH FROM to_date(concat(1980,3),'IYYYIW'));

  to_date   |  extract  
------------ -----------
 1980-01-14 | 316656000

If you're only interested in adding a certain amount of weeks to a date, just add an interval to it or use to_date with the pattern WW-YYYY (assuming string 3-1980):

SELECT 
  to_date('3-1980','WW-YYYY'),
  (to_date('1980','YYYY') interval '2 weeks')::date,
  EXTRACT(EPOCH FROM to_date('3-1980','WW-YYYY')),
  EXTRACT(EPOCH FROM (to_date('1980','YYYY') interval '2 weeks')::date);

  to_date   |    date    |  extract  |  extract  
------------ ------------ ----------- -----------
 1980-01-15 | 1980-01-15 | 316742400 | 316742400

Demo: db<>fiddle