0

Consider two dates, "01-Jan-2011' & '01-Oct-2011'. I wish to calculate number of weeks in between these dates.

I have tried the following:

select extract ( week from ( (current_date+ interval '5' day) - current_date ));
  • It returns error " no such unary operator 'week(day_interval)'"

I am able to find number of days by using following :

select extract ( day from ( (current_date+ interval '5' day) - current_date ));
  • the line above returns the output

Is there any way I can achieve the same?

Further, MonetDB considers week from Monday to Sunday(1-7). Is there any way this can be updated/ customised to Sunday to Saturday.

Thanks.

Aqua 4
  • 771
  • 2
  • 9
  • 26
ajaykr11cs
  • 3
  • 4
  • MonetDB doesn't support customising the starting day of the week yet. But some new features in this area will be introduced this year. I'll update the information after the release. – Jennie Feb 11 '21 at 08:54

1 Answers1

1

There are a couple of possibilities that I can think of:

select date '2011-10-01' - date '2011-01-01';

results in a INTERVAL DAY value, actually expressed in seconds of the difference, i.e. 23587200.000. This you could divide by (72460*60), i.e. the number of seconds in a week. But it's still an INTERVAL type, not an INTEGER.

Another way is to first convert the date to integers: the number of seconds since "the epoch" (Jan 1, 1970):

select epoch_ms(date '2011-10-01');

This actually give milliseconds since the epoch, so an extra factor of 1000.

This result you can then manipulate to get what you want:

select (epoch_ms(date '2021-02-02') - epoch_ms(date '2020-12-31')) / (7*24*60*60*1000);

This results in a HUGEINT value (if you have 128 bit integers in your system, i.e. anything compiled with GCC or CLANG), so you can convert this to INTEGER:

select cast((epoch_ms(date '2011-10-01') - epoch_ms(date '2011-01-01')) / (7*24*60*60*1000) as integer);
Sjoerd Mullender
  • 86
  • 1
  • Thanks @Sjoerd. I had tried the following to get the number of weeks : select cast((sys.epoch(current_date + interval '3' day) - sys.epoch(current_date)) / (7*24*60*60) as integer); But this doesn't handle the case where the difference between dates is less than 7 but belong to two different weeks. Consider the case : date1 as '12-02-2021'( belongs to week 6) and date2 as '15-02-2021'( belongs to week 7) If I execute the above query, it returns 0. I need some way so that number of week gets calculated considering this aspect as well and in the above case should return 1 as output. – ajaykr11cs Feb 12 '21 at 04:53