PostgreSQL: Unknown Date Output Format. How Can You Convert Date Output Format?

Question

I've been storing dates in a PostgreSQL 8.3 database table using a query resembling something like this:

INSERT INTO files (date_uploaded) VALUES (now());

According to the documentation there are four formats that PostgreSQL 8.3 will output depending on the setup. My select statement is returning an unknown fifth format:

01:10:00.57875+00

The select statement is a simple select, no funny business:

SELECT date_uploaded FROM files WHERE id = 1;

I would ultimately like to be able to output the datetime in a Unix timestamp format, however, any format that PHP's strtotime() function will work with would be acceptable.

---

Why isn't PostgreSQL outputting one of the four formats that are listed in the documentation?

How can I convert the "unknown" format to a different format, or change the default output format?

---

It seams I created the table column with time, instead of timestamp. This is why the format was messed up.

Answer 1

Not sure what's unknown about it, but anyway. If you want epoch just to:

select extract(epoch from date_uploaded) from files where id = 1;

The question is - why do you want epoch? It's not a format you can show to user, so why bother?