Postgres Pro
Downloads
Home   >   mailing lists

Thread: converting interval to timestamp

converting interval to timestamp

From
teknokrat
Date:
The difference of two dates/timestamps always gives an interval. is 
there a way to convert this interval into  number such as number of 
milliseconds or number of days?

Also does anyone know what field type an interval would map to in jdbc?

thanks

Re: converting interval to timestamp

From
Mike Rylander
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


If the number of seconds between timestamps is acceptable then this is the 
solution I use:

SELECT ABS(EXTRACT(EPOCH FROM t.field1) - EXTRACT(EPOCH FROM t.field2)) AS 
diff FROM table AS t;

Adjust to your column and WHERE needs.  One caveat:  this only works for dates 
within the UNIX epoch ( after 1969-12-31).

On Thursday 31 July 2003 12:05 pm, teknokrat wrote:
> The difference of two dates/timestamps always gives an interval. is
> there a way to convert this interval into  number such as number of
> milliseconds or number of days?
>
> Also does anyone know what field type an interval would map to in jdbc?
>
> thanks
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

- -- 
Mike Rylander
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE/KmAOgRDV1fFA+3cRAgbVAJ9B03Pxsn+N+Xg2C/a4gw3j28KSsgCeNA7+
y2rYedgRdTY/BiNSfVJTvOs=
=kVkm
-----END PGP SIGNATURE-----

Re: converting interval to timestamp

From
Jonathan Gardner
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 31 July 2003 09:05, teknokrat wrote:
> The difference of two dates/timestamps always gives an interval. is
> there a way to convert this interval into  number such as number of
> milliseconds or number of days?
>

Take a look at the User's Guide. There is great documentation on the available
functions to manipulate and extract useful information from dates and
timestamps.

I think the function you are looking for is "extract".

> Also does anyone know what field type an interval would map to in jdbc?
>

Post this question it the interfaces list. I am sure they would be more
helpful.

- --
Jonathan Gardner <jgardner@jonathangardner.net>
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/M84pWgwF3QvpWNwRAnOiAKC9t3rZNve8KyOgXyxMG/p75OxkTwCeP+Tv
ZYuvijykpHJ3GZiwD6hIvnI=
=onBr
-----END PGP SIGNATURE-----