9

I used following query to get current week

select extract(week from current_timestamp) and it is showing 34 thats fine.

But how do i get the current week number of the current month.

Prabhakaran
  • 3,900
  • 15
  • 46
  • 113
  • have you tried search for datepart(week,timestamp......), in sql server you can find the current week number by using datepart – zxc Aug 23 '13 at 09:46

6 Answers6

24

You can find the first day of this week with:

trunc('week', current_date)

And the first day of the first week of this month with:

trunc('week', date_trunc('month', current_date))

Subtract the two to find the in-month weeknumber:

extract('day' from date_trunc('week', current_date) -
                   date_trunc('week', date_trunc('month', current_date))) / 7 + 1
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • damn you beat me by 1 second :) +1 – Roman Pekar Aug 23 '13 at 10:11
  • 2
    Keep in mind this is the week number based on the yearly week number using ISO-8601. Not the week # based on the month itself.. so for instance: Today Feb 18th 2015 using the above method would return 4, whereas what I believe the poster was looking for was 3. – Altonymous Feb 18 '15 at 19:03
  • 1
    Be careful - this answer is wrong. Since the ISO first week of the year is the first week containing January 4th, any year where January 1st falls on Fri-Sun is going to break this. For example, if current_date was '2016-01-18' this method would return -49 as the week of the month since the 1st of January in 2016 is considered part of the last of week of 2015 in the ISO standard. Also note that the ISO week starts on Monday which can also cause confusion if you're in the habit of counting weeks from Sunday (see @Altonymous comment). – othp Apr 19 '17 at 16:33
9

Try the following

SELECT to_char('2016-05-01'::timestamp, 'W');
Aley
  • 8,540
  • 7
  • 43
  • 56
  • 1
    "W" = [week of month (1-5) (the first week starts on the first day of the month)](https://www.postgresql.org/docs/current/static/functions-formatting.html) So if the month starts on Friday, the next Thursday will still be week 1, and the next Friday will be the first day of week 2. A weird way to number weeks but might be what the OP is after :) – Andomar Apr 20 '17 at 08:39
2

You can find week number by using day as well like:

select ((date_part('day', current_date)::integer - 1) / 7) +1;
Shashank
  • 111
  • 1
  • 4
0

Im not sure on how does this work on postgreSql (http://www.w3cyberlearnings.com/PostgreSQL_DATE_PART)
but in sql server the example is something like this... 

>SELECT date_part('week', date)
zxc
  • 1,504
  • 3
  • 13
  • 32
  • @RelicSet oh i see did you mean for 1,2,3,4 for each month – zxc Aug 23 '13 at 10:06
  • @RelicSet sorry im not sure on how to do it on postgresqlbut you may wanna look at this site http://stackoverflow.com/questions/13116222/how-to-get-week-number-of-the-month-from-the-date-in-sql-server-2008 – zxc Aug 23 '13 at 10:09
0

This will give accurate results

CREATE OR REPLACE FUNCTION week_no(date) RETURNS integer AS $BODY$ SELECT CASE WHEN EXTRACT(DAY FROM $1::TIMESTAMP)::INTEGER = 1 THEN 1 ELSE extract(week from $1:: TIMESTAMP)::integer
- extract(week from ( date_trunc('month', $1::TIMESTAMP) + interval '1 day' ) )::integer + 1 END
$BODY$ LANGUAGE sql IMMUTABLE STRICT COST 100;

Example: week_no('2017-01-01') = 1

Prahlad Rao
  • 34
  • 6
0

Maybe late.. but this works too

where cast(call_start as Date) = Date 'today'

or cast(call_start as Date)= Date 'today' -1

or cast(call_start as Date)= Date 'today' -2

or cast(call_start as Date)= Date 'today' -3

or cast(call_start as Date)= Date 'today' -4

or cast(call_start as Date)= Date 'today' -5

or cast(call_start as Date)= Date 'today' -6

or cast(call_start as Date)= Date 'today' -7

;

this gives me it

L_GIB
  • 125
  • 7