Hacker News Reader

Comment by rainbowliquor | Hacker News Reader

date_trunc() is one way but to_char is even better as you can get the resulting output to something nicer. Doing:

  SELECT DATE_TRUNC('week', CURRENT_TIMESTAMP);

gives:

  2017-06-05 00:00:00+00

vs:

  SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-WW"wk"');

gives:

  2017-23wk

View on HN

Note that there's the ISO standard for weeks which uses slightly different abbreviations:

    SELECT to_char(now(), 'IYYY-"W"IW');

The difference is when the first week of the year starts. Compare yours to the ISO 8601 format for January 1 this year:

    $ SELECT to_char('2017-01-01'::date, 'IYYY-"W"IW');
    > 2016-W52

    $ SELECT to_char('2017-01-01'::date, 'YYYY-"W"WW');
    > 2017-W01

It's not the same: date_trunc returns timestamp, to_char returns a string.

True but in the use case of OPs example. In the article OP says "So if we wanted to find the count of users that signed up per week:" which "2017-06-05 00:00:00+00" isn't a week, it's a date (with a time stamp as well which isn't pertinent) which happens to be the beginning of a week. Using TO_CHAR() with a format string makes it more legible and more recognizable.

Reply on news.ycombinator.com