I have a PHP script that captures the time using the PHP time() function. I
want to insert this time into a postgresql table timestamp field. I am at a
loss on how to do this. For example I want to do something like the
following...

<?php

$time_started = time();

INSERT INTO surveys (name, time_started, time_ended) VALUES
('somename',$time_start,$time_ended);
?>

Both the "time_started" and "time_ended" fields are defined as postgresql
timestamps.

The problem is that the "$time_started" variable is seconds since the epoch,
and postgresql wants a timestamp value. Isn't there a simple way to do this
inside the INSERT script without having to resort to application level
scripting???

I have been searching around the net, and looked at the manual but have had
no luck finding a solution.

Thanks in advance,

Alan



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On 4 Apr 2004, at 12:08, Alan T. Miller wrote:

If you have a Unix-style timestamp (seconds since 1970-01-01 00:00) you
can convert it to a PostgreSQL timestamp like this:

select timestamp '1970-01-01' + interval '953559481 seconds';

There might be a better way, but that's how I solved it.
---
Richard Brooksby <rb (AT) ravenbrook (DOT) com>
Senior Consultant
Ravenbrook Limited <http://www.ravenbrook.com/>
PO Box 205, Cambridge CB2 1AN, United Kingdom
Voice: +44 777 9996245 Fax: +44 870 1641432


---------------------------(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

Richard Brooksby <rb (AT) ravenbrook (DOT) com> writes:
If it's really truly a Unix timestamp, that is seconds since 1970-01-01
00:00 GMT, you need to say "timestamptz" or "timestamp with time zone"
and specify that you want GMT zone. The above references the interval
to 1970-01-01 midnight your local time, and will therefore be wrong by
the amount of your offset from GMT.

The recommended way is really

select timestamptz 'epoch' + 953559481 * interval '1 second';

where 'epoch' is just a slightly more mnemonic way of writing
'1970-01-01 00:00 GMT'. Using the number-times-interval operator as
I've done here is optional, but you'll find it's a good habit to get
into, because this way is much more convenient as soon as you start
doing anything even a little bit complicated. The other way tends to
lead you into wanting to do ugly, error-prone things with concatenating
strings together and then converting them to interval...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster