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.

This looks like a time output, is that column really a date column ? Postgresql allows you to format it almost however you want though , see postgresql.org/docs/8.3/interactive/functions-datetime.html — nos
– nos, Commented Jul 4, 2009 at 20:24
As stated in the bottom section of my question, yes, that was the problem I was having. Thanks for the link though, it'll help in the future. — Ty.
– Ty., Commented Jul 5, 2009 at 3:22

user80168 · Accepted Answer · 2009-07-04 13:52:22Z

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?

answered Jul 4, 2009 at 13:52

user80168

Sign up to request clarification or add additional context in comments.

1 Comment

Ty. Over a year ago

It seams I created the table column with time, instead of timestamp. Your solution works once the column type was updated. Thank you.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related