Re: [SQL] time intervals - Mailing list pgsql-sql
| From | José Soares |
|---|---|
| Subject | Re: [SQL] time intervals |
| Date | |
| Msg-id | 3729A5FF.F424F0CD@sferacarta.com Whole thread Raw |
| In response to | Re: [SQL] time intervals (Mark Jewiss <mark@office.knowledge.com>) |
| List | pgsql-sql |
Mark Jewiss ha scritto: <blockquote type="CITE">> I have two fields, timein & timeout, both defined as type "time". how can <br />> i get the difference between the two?? i am connecting to postgres through <br />> access97and trying to run a simple query to show me timeout, timein, and <br />> timeout-timein, but the calculation isreturning an error. <p>In SQL you can do a DATEDIFF...I'm not in front of postgresql so am not <br />sure if it supportsthis. <br /> </blockquote> DATEDIFF is not a SQL command maybe an Oracle or Sybase command. <blockquote type="CITE"> <br />Syntax is <p>select datediff (day, mydate, 'Jan 10 1999' <br />from table <p>If mydate = Jan 9, then theresult is one. I think datediff accepts <br />datetime, so if you specify it properly (i.e. '1 Jan 1999 10:05") then <br/>you'll get the right answers. Just change day to whatever you need. <p>Regards, <p>Mark. <br />-- <br />Mark Jewiss<br />Knowledge Matters Limited</blockquote><tt>You can create an operator for TIME - TIME...</tt><tt></tt><p><tt>-- Arithmetic operations that are permitted by SQL92:</tt><br /><tt>-- -----------------------------------------</tt><br/><tt>-- 1st operand|operator|2nd operand|result</tt><br /><tt>-- -----------+--------+-----------+--------</tt><br/><tt>-- datetime | - |datetime |interval</tt><br /><tt>-- datetime | + |interval |datetime</tt><br /><tt>-- datetime | - |interval |datetime</tt><br /><tt>-- interval | + |datetime |datetime</tt><br /><tt>-- interval | + |interval |interval</tt><br /><tt>-- interval | - |interval |interval</tt><br /><tt>-- interval | * |number |interval</tt><br /><tt>-- interval | / |number |interval</tt><br /><tt>-- number | * |interval |interval</tt><br /><tt>-- -----------+--------+-----------+--------</tt><tt></tt><p><tt>--TIME- TIME = INTERVAL-------------------------------------------</tt><br/><tt>create function time_mi_time(time,time) returns timespanas '</tt><br /><tt>declare</tt><br /><tt> i1 interval;</tt><br /><tt> i2 interval;</tt><br /><tt>begin</tt><br/><tt> i1:= $1;</tt><br /><tt> i2:= $2;</tt><br /><tt> i1:=i1 - i2;</tt><br /><tt> return i1;</tt><br /><tt>end;' language 'plpgsql';</tt><tt></tt><p><tt>create operator - (</tt><br /><tt> leftarg=time,</tt><br /><tt> rightarg=time,</tt><br /><tt> procedure=time_mi_time,</tt><br /><tt> commutator='-',</tt><br /><tt> negator='+',</tt><br /><tt> restrict=eqsel,</tt><br /><tt> join=eqjoinsel</tt><br /><tt> );</tt><tt></tt><p><tt>hygea=> select time '12:30' - time '12:00';</tt><br/><tt>?column?</tt><br /><tt>--------</tt><br /><tt>00:30:00</tt><br /><tt>(1 row)</tt><tt></tt><p><tt>--</tt><br/><tt>______________________________________________________________</tt><br /><tt>PostgreSQL6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3</tt><br /><tt>^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^</tt><br/><tt>Jose'</tt><br /><tt></tt>