1. Forum
  2. Usenet
  3. COMP.DATABASES.POSTGRESQL

  • work with intervals

    From Magnus Warker@21:1/5 to All on Wed Jan 6 13:39:51 2016
    Hi,

    I would like to store intervals in a table.

    When I write '55:39', it's shown (in pgAdmin) as '55:39'. So it's not
    somewhat normalized.

    What does it mean then? 55 minutes and 39 seconds or 55 hours and 39
    minutes?

    When I create a view that divides two intervals, I get results like '08:23:01.543116' (shown in the view in pgAdmin).
    Can I format it somehow to show only minutes and seconds, preferrably in
    the view itself?

    Thanks
    Magnus

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Harry Tuttle@21:1/5 to Magnus Warker on Fri Jan 8 10:46:46 2016
    Magnus Warker schrieb am 06.01.2016 um 13:39:
    Hi,

    I would like to store intervals in a table.

    When I write '55:39', it's shown (in pgAdmin) as '55:39'. So it's not somewhat normalized.

    What does it mean then? 55 minutes and 39 seconds or 55 hours and 39 minutes?

    When I create a view that divides two intervals, I get results like '08:23:01.543116' (shown in the view in pgAdmin).
    Can I format it somehow to show only minutes and seconds, preferrably in the view itself?

    You can use the justify_xxx() functions for this:

    select justify_hours(interval '55:43')

    returns: 0 years 0 mons 2 days 7 hours 43 mins 0.00 secs

    Details in the manual: http://www.postgresql.org/docs/current/static/functions-datetime.html

    I don't know what you mean with "dividing" intervals.
    Dividing a duration by another does not really make sense to me....


    Btw: you are betting off posting to the Postgres mailing lists than to comp.databases.postgresql.

    If you prefer newsgroup style reading (as I do) you can register with the mailing list with the option to not deliver mail, and then subscribe to the ntp mirrors of the mailing lists on news.gmane.org

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Harry Tuttle@21:1/5 to Magnus Warker on Sat Jan 9 10:07:55 2016
    Magnus Warker schrieb am 09.01.2016 um 06:56:
    select justify_hours(interval '55:43')
    returns: 0 years 0 mons 2 days 7 hours 43 mins 0.00 secs

    So '55:39' means 55 hours and 39 minutes?
    Does this mean that when leaving out the microseconds the right most number is treated as minutes?

    What do I have to enter (in pgAdmin) if I want 53 minutes and 51 seconds?

    There are several different ways of specfiying an interval:

    select interval '00:53:51'

    or

    select interval '54H 51s'


    Details in the manual: http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-INTERVAL-INPUT


    When I enter "53:51" it is saved as is. Do I have to change it to "53:51:00" then?
    The same vor timestamps:
    I enter "2016-01-09 05:26" and pgAdmin stores this value exactly as I entered it. Do I have to change it to "2016-01-09 05:26:00" then?

    I don't use pgAdmin.

    A timestamp is always saved with all parts (but without any formatting).

    My guess is that pgAdmin simply does not display the seconds.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Magnus Warker@21:1/5 to Harry Tuttle on Tue Jan 19 06:43:15 2016
    On 01/09/2016 10:07 AM, Harry Tuttle wrote:

    Details in the manual: http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-INTERVAL-INPUT

    Sorry, I cannot apply this documnentation to my case: I have no symbols
    like 'P' or 'year' in my intervals.

    Assume the following select in psql:


    mydb=# select duration from vds_run;
    duration
    ----------
    00:55:39
    00:55:25
    00:53:44
    00:53:51
    00:57:05
    00:57:42
    01:01:17
    01:03:17

    What durations do we see here?
    hour:minute:second?

    Is the last one "1 hour, 3 minutes and 17 seconds"?

    Thanks
    Magnus

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Dimitri Fontaine@21:1/5 to Magnus Warker on Tue Jan 19 10:20:49 2016
    Magnus Warker <magnux@mailinator.com> writes:
    mydb=# select duration from vds_run;
    duration
    ----------
    00:55:39

    What durations do we see here?
    hour:minute:second?

    # set intervalstyle to 'postgres_verbose';
    SET
    # select interval '00:55:39';
    interval
    -------------------
    @ 55 mins 39 secs
    (1 row)

    --
    Dimitri Fontaine
    PostgreSQL DBA, Architecte

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Magnus Warker@21:1/5 to Dimitri Fontaine on Sun Jan 31 06:04:50 2016
    On 01/19/2016 10:20 AM, Dimitri Fontaine wrote:
    Magnus Warker <magnux@mailinator.com> writes:

    # set intervalstyle to 'postgres_verbose';
    SET
    # select interval '00:55:39';
    interval
    -------------------
    @ 55 mins 39 secs
    (1 row)

    That's great!
    Thank you!

    Magnus

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Magnus Warker@21:1/5 to Harry Tuttle on Fri Jan 8 19:57:00 2016
    On 01/08/2016 10:46 AM, Harry Tuttle wrote:
    Magnus Warker schrieb am 06.01.2016 um 13:39:

    I don't know what you mean with "dividing" intervals.
    Dividing a duration by another does not really make sense to me....

    Sorry, I meant I divide an interval by a distance, e. g. 59:35 / 9.4 km.

    Btw: you are betting off posting to the Postgres mailing lists than to comp.databases.postgresql.

    If you prefer newsgroup style reading (as I do) you can register with
    the mailing list with the option to not deliver mail, and then subscribe
    to the ntp mirrors of the mailing lists on news.gmane.org

    Ok, I'll try that.

    Magnus

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Magnus Warker@21:1/5 to Harry Tuttle on Sat Jan 9 06:56:14 2016
    On 01/08/2016 10:46 AM, Harry Tuttle wrote:
    Magnus Warker schrieb am 06.01.2016 um 13:39:

    When I write '55:39', it's shown (in pgAdmin) as '55:39'. So it's not
    somewhat normalized.

    What does it mean then? 55 minutes and 39 seconds or 55 hours and 39
    minutes?

    select justify_hours(interval '55:43')
    returns: 0 years 0 mons 2 days 7 hours 43 mins 0.00 secs

    So '55:39' means 55 hours and 39 minutes?
    Does this mean that when leaving out the microseconds the right most
    number is treated as minutes?

    What do I have to enter (in pgAdmin) if I want 53 minutes and 51 seconds?

    When I enter "53:51" it is saved as is. Do I have to change it to
    "53:51:00" then?

    The same vor timestamps:
    I enter "2016-01-09 05:26" and pgAdmin stores this value exactly as I
    entered it. Do I have to change it to "2016-01-09 05:26:00" then?

    Thanks
    Magnus

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)