pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Porting from mysql to psql (UNIX_TIMESTAMP()?)
4+ messages / 3 participants
[nested] [flat]

* Porting from mysql to psql (UNIX_TIMESTAMP()?)
@ 2000-09-09 11:02  Zlatko Calusic <zlatko@iskon.hr>
  0 siblings, 3 replies; 4+ messages in thread

From: Zlatko Calusic @ 2000-09-09 11:02 UTC (permalink / raw)
  To: pgsql-sql; pgsql-general@postgresql.org

Hi!

As subject says, we are currently porting all of our data, programs
and logic from mysql to postgresql. One of the things we have yet to
resolve is how to replace mysql's UNIX_TIMESTAMP() function we used
extensively in PosgreSQL?

Function works like this in mysql:

mysql> select start from connection limit 1;
+---------------------+
| start               |
+---------------------+
| 2000-07-03 20:12:37 |
+---------------------+
1 row in set (0.01 sec)

mysql> select UNIX_TIMESTAMP(start) from connection limit 1;
+-----------------------+
| UNIX_TIMESTAMP(start) |
+-----------------------+
|             962647957 |
+-----------------------+
1 row in set (0.00 sec)

Is there any similar functionality (returning unixish number of
seconds since 1970 from the timestamp field) in PostgreSQL?

I tried all of the available date/time functions, type casting but all
to no avail.

TIA,
-- 
Zlatko

P.S Is it bad manners crossposting to two pgsql mailing list? Still
new to PostgreSQL, still learning...



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Porting from mysql to psql (UNIX_TIMESTAMP()?)
@ 2000-09-09 11:25  Rommel B. Abaya <rommel.abaya@ramcargroup.com>
  parent: Zlatko Calusic <zlatko@iskon.hr>
  2 siblings, 0 replies; 4+ messages in thread

From: Rommel B. Abaya @ 2000-09-09 11:25 UTC (permalink / raw)
  To: zlatko@iskon.hr; +Cc: pgsql-sql; pgsql-general@postgresql.org

i thick it's epoch() in PostgreSQL....check your documentation.



Zlatko Calusic wrote:

> Hi!
>
> As subject says, we are currently porting all of our data, programs
> and logic from mysql to postgresql. One of the things we have yet to
> resolve is how to replace mysql's UNIX_TIMESTAMP() function we used
> extensively in PosgreSQL?
>
> Function works like this in mysql:
>
> mysql> select start from connection limit 1;
> +---------------------+
> | start               |
> +---------------------+
> | 2000-07-03 20:12:37 |
> +---------------------+
> 1 row in set (0.01 sec)
>
> mysql> select UNIX_TIMESTAMP(start) from connection limit 1;
> +-----------------------+
> | UNIX_TIMESTAMP(start) |
> +-----------------------+
> |             962647957 |
> +-----------------------+
> 1 row in set (0.00 sec)
>
> Is there any similar functionality (returning unixish number of
> seconds since 1970 from the timestamp field) in PostgreSQL?
>
> I tried all of the available date/time functions, type casting but all
> to no avail.
>
> TIA,
> --
> Zlatko
>
> P.S Is it bad manners crossposting to two pgsql mailing list? Still
> new to PostgreSQL, still learning...




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Porting from mysql to psql (UNIX_TIMESTAMP()?)
@ 2000-09-09 12:30  Zlatko Calusic <zlatko@iskon.hr>
  parent: Zlatko Calusic <zlatko@iskon.hr>
  2 siblings, 0 replies; 4+ messages in thread

From: Zlatko Calusic @ 2000-09-09 12:30 UTC (permalink / raw)
  To: pgsql-sql; +Cc: pgsql-general@postgresql.org

Zlatko Calusic <zlatko@iskon.hr> writes:

> Is there any similar functionality (returning unixish number of
> seconds since 1970 from the timestamp field) in PostgreSQL?

It's amazing that I spent something like an hour trying to find an
answer, and only ten minutes waiting for the answer to it. :)

Mario Weilguni came up with a date_part('epoch', start) solution, and
that is exactly what I was looking for.

Thanks Mario!
-- 
Zlatko



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Porting from mysql to psql (UNIX_TIMESTAMP()?)
@ 2000-09-09 16:59  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Zlatko Calusic <zlatko@iskon.hr>
  2 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2000-09-09 16:59 UTC (permalink / raw)
  To: zlatko@iskon.hr; +Cc: pgsql-sql; pgsql-general@Postgresql.org

Zlatko Calusic <zlatko@iskon.hr> 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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2000-09-09 16:59 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-09-09 11:02 Porting from mysql to psql (UNIX_TIMESTAMP()?) Zlatko Calusic <zlatko@iskon.hr>
2000-09-09 11:25 ` Rommel B. Abaya <rommel.abaya@ramcargroup.com>
2000-09-09 12:30 ` Zlatko Calusic <zlatko@iskon.hr>
2000-09-09 16:59 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox