Обсуждение: php, time and postgresql

I want to save some time information in postgresql with php. What is the
datatype in postgresql coresponding to the mktime()-unixtime function in
php (creates a time in the format "979333398" eg.)?

Thanx!

How about doing something like this:
bruno=> select 'epoch'::timestamp + '979333398 second';
        ?column?
------------------------
 2001-01-12 15:03:18-06

On Fri, Jan 12, 2001 at 10:03:54PM +0100,
  Rasmus Resen Amossen <spunk@rhk.dk> wrote:
> I want to save some time information in postgresql with php. What is the
> datatype in postgresql coresponding to the mktime()-unixtime function in
> php (creates a time in the format "979333398" eg.)?
>
> Thanx!

Rasmus Resen Amossen <spunk@rhk.dk> writes:
> I want to save some time information in postgresql with php. What is the
> datatype in postgresql coresponding to the mktime()-unixtime function in
> php (creates a time in the format "979333398" eg.)?

You can coerce an integer representing a Unix timestamp to abstime,
and thence to any other datetime datatype you might want to use.

play=> select now()::abstime::integer;
 ?column?
-----------
 979337141
(1 row)

play=> select 979337141::integer::abstime;
        ?column?
------------------------
 2001-01-12 17:05:41-05
(1 row)

The "official" way to get from a datetime type to a Unix timestamp is
date_part('epoch', timevalue):

play=> select date_part('epoch', now());
 date_part
-----------
 979337212
(1 row)

but I don't know of any easy way to go in the other direction except by
casting to abstime.

            regards, tom lane

On Fri, Jan 12, 2001 at 02:29:01PM -0700,
  Mark Lane <mlane@mynewthing.com> wrote:
> On Friday 12 January 2001 14:29, you wrote:
> > How about doing something like this:
> > bruno=> select 'epoch'::timestamp + '979333398 second';
> >         ?column?
> > ------------------------
> >  2001-01-12 15:03:18-06
> >
> I Think that is an interesting way of converting unixtime to a timestamp but
> I think he wants to save the time as 979333398 seconds. That would allow him
> to easily convert it to any date/time format when he retrieves it from the
> Database It would also allow for faster sorting if he stored the information
> as an int.

I don't see how storing the time in seconds helps. Once you do that you
can't use the to_date functions to format the output. I also don't see
gaining much in terms of speed either. Timestamps are wider than ints,
but are going to be fast to work with.

While looking for ideas about this I noticed a lack of to_date functions for
printing intervals. I think it would be nice to be able to do something like
the following:
select to_char(now() - 'epoch', 'SSSSSSSSSSSSS');
and get the result:
979333398