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)

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)

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)

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)

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)

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)

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)

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)