CODEWITHSUNDEEP
mysqlpostgresqldate-conversion
OAK
OAK

Reputation: 3166

PostgreSQL Convert date (YYYY-WW) vs MySQL ('%x-%v')

I'm trying to convert a query from MySQL to PostgreSQL. The results vary slightly as it appears there is a difference between how the two languages define a week out of the year and which days those include.

Same logic gives different date results:

PostgreSQL Syntax -

select date from sales where to_char(date, 'YYYY-WW') >= '2017-51' AND to_char(date, 'YYYY-WW') <= '2017-52'

MySQL Syntax - 

select date from sales where 
    DATE_FORMAT(date, '%x-%v') >='2017-51' and DATE_FORMAT(date, '%x-%v') <='2017-52'

When I query the PostgeSQL the results are 2017-12-17: 2017-12-24. MySQL results are 2017-12-18: 2017-12-25.

Why is there a difference here?

Upvotes: 1

Views: 2363

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51599

https://www.postgresql.org/docs/10/static/functions-formatting.html

WW week number of year (1-53) (the first week starts on the first day of the year)

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

%v Week (01..53), where Monday is the first day of the week

so this behaviour is expected, because for postgres 

t=# select to_char('2017-12-17'::date,'Day'), to_char('2017-12-17'::date,'WW');
  to_char  | to_char
-----------+---------
 Sunday    | 51
(1 row)

because:

t=# select to_char('2017-01-01'::date,'Day'), to_char('2017-01-01'::date,'WW');
  to_char  | to_char
-----------+---------
 Sunday    | 01
(1 row)

while for MySQL:

select DATE_FORMAT('2017-01-01', '%x-%v');

is

2016-52

and thus 2017-12-17is week 50

Upvotes: 2

Related Questions