[Nottingham] [OT, Humour] Two timely gems...

[Martin]

Folks,

Just a little giggle or two from Google whilst musing that there "must
be a better way" and...

So in PostgreSQL, how do you easily convert a time interval into a
fractional hours value?

There's the brute force way of adding up:
( days * 24 + hours + minutes / 60 + seconds / 3600 ... )

Or...


http://stackoverflow.com/questions/952493/how-do-i-convert-an-interval-into-a-number-of-hours-with-postgres

####
Probably the easiest way is:

SELECT EXTRACT(epoch FROM my_interval)/3600
####

Which is spot on!


The giggles are:

####
Extract epoch? Oh my, that wouldn't have crossed my mind in a million years
####

:-)


There's also the fun with:

http://www.postgresql.org/docs/8.2/static/functions-datetime.html

####
9.9.1. EXTRACT, date_part

EXTRACT(field FROM source)

...valid field names:

century

    The century

    SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
    Result: 20
    SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
    Result: 21

    The first century starts at 0001-01-01 00:00:00 AD, although they
did not know it at the time. This definition applies to all Gregorian
calendar countries. There is no century number 0, you go from -1 to 1.
If you disagree with this, please write your complaint to: Pope,
Cathedral Saint-Peter of Roma, Vatican.

    PostgreSQL releases before 8.0 did not follow the conventional
numbering of centuries, but just returned the year field divided by 100.
####


... And then there is:

$ cal 9 1752
   September 1752
 S  M Tu  W Th  F  S
       1  2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30


Quite a headache!


Is that why computer Geeks use the epoch instead?!

Must be time for a beer!!

Cheers,
Martin

-- 
----------------
Martin Lomas
martin at ml1.co.uk
----------------