Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Working with time in Postgres

craigkerstiens.com

Post reply on HN
Print view View original

11–20 of 128 posts

Re: Working with time in Postgres

#11

Post by manigandham »

lacampbell wrote: post #10
deepsun wrote: post #8

Last time I checked, I couldn't store a datetime _with_ timezone. It was really strange that such a powerful database doesn't support storing full-ISO datetimes, like '2017-01-01T00:00:00Z'. Instead, it converted it to date-time-only instant, losing information of original timezone along the way. Sure, I could fetch it back using any timezone I want, but I really wanted to know the original timezone it was in.

Would it be that much work to add a smallint field, that had the original UTC offset used for your time?

Timezones are more than just an offset.

Re: Working with time in Postgres

#12

Post by lacampbell »

manigandham wrote: post #11
lacampbell wrote: post #10

Earlier quoted context omitted.

Would it be that much work to add a smallint field, that had the original UTC offset used for your time?

Timezones are more than just an offset.

True.

I'm sure a use case exists where you want to store a timezone, but I don't know what it is. I try and use UTC everywhere, and only worry about time zones when displaying.

Re: Working with time in Postgres

#13

Post by revicon »

Weird, the example in the post (after changing table/field names for my database)

  with weeks as (
    select week as week
    from generate_series('2017-01-01'::date, now()::date, '1 week'::interval) weeks
  ),

  SELECT weeks.week,
    count(*)
  FROM weeks,
    test_results
  WHERE
    test_results.date_created > weeks.week
  AND
    test_results.date_created
Throws an error for me...

  ERROR:  syntax error at or near "SELECT"
  LINE 5: SELECT weeks.week,
          ^

Re: Working with time in Postgres

#14

Post by rainbowliquor »

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

Re: Working with time in Postgres

#15

Post by 0x0 »

lacampbell wrote: post #12
manigandham wrote: post #11

Earlier quoted context omitted.

Timezones are more than just an offset.

True. I'm sure a use case exists where you want to store a timezone, but I don't know what it is. I try and use UTC everywhere, and only worry about time zones when displaying.

Any future event (such as a meeting) can't be stored as UTC, because time zone rules may change between now and the event date, but the event still needs to happen 10am local time.

Re: Working with time in Postgres

#16

Post by cmaggard »

revicon wrote: post #13

Weird, the example in the post (after changing table/field names for my database) with weeks as ( select week as week from generate_series('2017-01-01'::date, now()::date, '1 week'::interval) weeks ), SELECT weeks.week, count(*) FROM weeks, test_results WHERE test_results.date_created > weeks.week AND test_results.date_created Throws an error for me... ERROR: syntax error at or near "SELECT" LINE 5: SELECT weeks.week…

Remove the comma before the SELECT?

Re: Working with time in Postgres

#17

Post by barsonme »

revicon wrote: post #13

Weird, the example in the post (after changing table/field names for my database) with weeks as ( select week as week from generate_series('2017-01-01'::date, now()::date, '1 week'::interval) weeks ), SELECT weeks.week, count(*) FROM weeks, test_results WHERE test_results.date_created > weeks.week AND test_results.date_created Throws an error for me... ERROR: syntax error at or near "SELECT" LINE 5: SELECT weeks.week…

yeah, the comma after the "with" block shouldn't be there.

i.e.,

    ... weeks
    )
    SELECT weeks.week

Re: Working with time in Postgres

#18

Post by extesy »

rainbowliquor wrote: post #14

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

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

Re: Working with time in Postgres

#19

Post by lacampbell »

0x0 wrote: post #15
lacampbell wrote: post #12

Earlier quoted context omitted.

True. I'm sure a use case exists where you want to store a timezone, but I don't know what it is. I try and use UTC everywhere, and only worry about time zones when displaying.

Any future event (such as a meeting) can't be stored as UTC, because time zone rules may change between now and the event date, but the event still needs to happen 10am local time.

Learned something new today! Surprised that never crossed my mind, even though I experience daylight saving every year.

Re: Working with time in Postgres

#20

Post by revicon »

barsonme wrote: post #17
revicon wrote: post #13

Weird, the example in the post (after changing table/field names for my database) with weeks as ( select week as week from generate_series('2017-01-01'::date, now()::date, '1 week'::interval) weeks ), SELECT weeks.week, count(*) FROM weeks, test_results WHERE test_results.date_created > weeks.week AND test_results.date_created Throws an error for me... ERROR: syntax error at or near "SELECT" LINE 5: SELECT weeks.week…

yeah, the comma after the "with" block shouldn't be there. i.e., ... weeks ) SELECT weeks.week

makes sense. After removing it...

  with weeks as (
    select week as week
    from generate_series('2017-01-01'::date, now()::date, '1 week'::interval) weeks
  )

  SELECT weeks.week,
    count(*)
  FROM weeks,
    test_results
  WHERE
    test_results.date_created > weeks.week
  AND
    test_results.date_created
it throws...

  ERROR:  column "week" does not exist
  LINE 2:     select week as week
                     ^
I would move this to the post's own "replies" section, but it doesn't have one.
Post reply on HN