3 ms·
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
by rainbowliquor 9y ago
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
- extesy 9y agoIt's not the same: date_trunc returns timestamp, to_char returns a string.
- rainbowliquor 9y agoTrue 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.
- fphilipe 9y agoNote 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