php, time and postgresql

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:

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

---------- Forwarded Message ----------
Subject: Re: [GENERAL] php, time and postgresql
Date: Fri, 12 Jan 2001 14:13:21 -0700
From: Mark Lane <mlane@mynewthing.com>
To: Rasmus Resen Amossen <spunk@rhk.dk>

On Friday 12 January 2001 14:03, you 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!

If you want to save time in that format just use an int4. All you have there
is an integer value so you will not need a special datatype. With datetime
information you only need a special datatypes when you storing information in
a form such as provided by timestamp.

Mark

-------------------------------------------------------