Postgres and the UNIX time stamp

[Anon]

I'm writing a PHP app with a postgresql backend that stores the time each record is called. When I pull the date back (and echo it) I get this:

2001-09-08 21:07:56+01

When I run it though strtotime() I get this:

-1

(I have no idea if that is normal or not). When I then put that time though date("d/M/Y") I get:

01/Jan/1970

Can anyone see what I am doing wrong? I've heard that strtotime() is broken on some versions of PHP (I'm using whatever comes with Debian Potato) but apparently its possible to get postgresql to return a UNIX timestamp:

http://www.archonet.com/pgdocs/date-to-epoch.html

although I have no idea how to merge that with the SELECT query I already have:

SELECT * FROM data ORDER BY is DESC LIMIT $number OFFSET $offset

Can someone either give me a fix to my PHP or my SQL?

[toma42]

CREATE FUNCTION unix_timestamp (timestamp) RETURNS float8 AS 'SELECT date_part(\'epoch\', $1) AS RESULT;' LANGUAGE 'sql';

Please make a comment in your app attributing this function to me, [e-mail]

[[deleted]]

How modest 🙂

http://www.postgresql.org/idocs/index.php?functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

A much simpler and waaaay more compatible method is to store the time stamp as an integer. That way PHP can store the unix timestamp and all incompatibilities disappear.

But if you do that, be sure to attribute the idea to me: [e-mail] :-)

[toma42]

Hey man, not trying to be a dick about it. I took a couple hours to come up with that function and I'm rather proud of it. I felt my contribution here was done as a solution rather than an idea...

The source code I've released for my personal web site, db.etree.org, is listed with this same request. If I had only posed an idea then I would not have been so unmodest.

Tom

[Anon]

I've never used SQL functions, and have no idea how to implement this. Can you provide example PHP code to demonstrate please?

This is what I have at the moment:

$sqlconnection = "host=localhost user=nobody dbname=david-dblog";

$conn = pg_connect($sqlconnection) or die("Unable to connect to database server");

$sql= "SELECT * FROM data ORDER BY id DESC LIMIT 2 OFFSET 2"; // The limit and offset are generated dynamically

$result_set = pg_Exec($conn, $sql);

// And then deal with that $result_set

[toma42]

The SQL function only needs to be ran once. This will create a function called unix_timestamp that will work the same way as the mysql function of the same name.

An example of its use:

select unix_timestamp(datetime_column) from table;

[[deleted]]

Sorry Tom, but there is an annotation in the postgres manual that looks about 75% the same as this so I figured you just copied that. 🙂

Your effort is appreciated!

[toma42]

Yea, I knew once it was done that it was right so I'm not suprised at all that an annotation like that exists.

When I put this together there weren't user comments in the manual...I did it for this project (which I was going to submit as an article here at one time):

http://db.etree.org/dbadmin/migrating_a_php_application.html
http://db.etree.org/wddx/mysql-psql.phps

I developed that function for the .phps linked above. Maybe I'm trying to show off now...basicly I just wanted to defend myself.

Later,
Tom

[Anon]

So I can just log in to the database with "psql databasename" then run that command?

Then would I use:

"select id, unix_timestamp(date), subject, entry from table news"?

When I try to run the function command I get this error:

david-dblog=> CREATE FUNCTION unix_timestamp (timestamp) RETURNS float8 AS 'SELECT date_part(\'epoch\', $1) AS RESULT;' LANGUAGE 'sql';
ERROR: Function 'date_part(text, timestamp)' does not exist
Unable to identify a function which satisfies the given argument types
You will have to retype your query using explicit typecasts