Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e89Gxpf97030; Sat, 9 Sep 2000 12:59:51 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id MAA29368; Sat, 9 Sep 2000 12:59:47 -0400 (EDT) To: zlatko@iskon.hr cc: pgsql-sql@Postgresql.org, pgsql-general@Postgresql.org Subject: Re: [SQL] Porting from mysql to psql (UNIX_TIMESTAMP()?) In-reply-to: <874s3p3k1c.fsf@atlas.iskon.hr> References: <874s3p3k1c.fsf@atlas.iskon.hr> Comments: In-reply-to Zlatko Calusic message dated "09 Sep 2000 13:02:39 +0200" Date: Sat, 09 Sep 2000 12:59:47 -0400 Message-ID: <29365.968518787@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200009/225 Zlatko Calusic writes: > Is there any similar functionality (returning unixish number of > seconds since 1970 from the timestamp field) in PostgreSQL? Sure. You can use date_part, or cast to abstime and thence to integer: regression=# select now(); now ------------------------ 2000-09-09 12:55:50-04 (1 row) regression=# select date_part('epoch',now()); date_part ----------- 968518563 (1 row) regression=# select now()::abstime::int4; ?column? ----------- 968518585 (1 row) To go the other way (integer seconds to timestamp), use the cast method in reverse: regression=# select 968518585 :: int4 :: abstime :: timestamp; ?column? ------------------------ 2000-09-09 12:56:25-04 (1 row) (there's probably a cleaner way to do this, but that works ...) regards, tom lane