Working with time in Postgres

Re:Working with time in Postgres by rainbowliquor on Thursday June 08, 2017 @06:50PM (#14517777)

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

[ Parent | on HN ]

Re:Working with time in Postgres by fphilipe on Friday June 09, 2017 @02:30AM (#14519536)

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

[ Parent | on HN ]

Re:Working with time in Postgres by extesy on Thursday June 08, 2017 @06:53PM (#14517794)

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

[ Parent | on HN ]

Re:Working with time in Postgres by rainbowliquor on Thursday June 08, 2017 @07:00PM (#14517846)

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.

[ Parent | on HN ]