Ok, here's what I want to do:

  1. Insert a date time entry (without time zone!) into PostgreSQL.

  2. Get the date time field back out in a format that I can plug into PHP's date() function for formatting.

I am having a heck of a time getting a date/time field into PostgreSQL without the time zone. I've tried using now(), date_trunc('second',now()), and timestamp(current_date, current_time). They all contain a time zone, and when I plug that into date() for formatting, it gives me the crappy default 1969 date.

I suppose I could convert to a string and cut off the time zone, but who wants to do that EVERY TIME? I just need a way to get date and time into the field, sans time zone.

Any help greatly appreciated,
Chris McCormick

    That's because you don't have the power of this:

    / unix_timestamp for pgsql by toma@etree.org /
    CREATE FUNCTION unix_timestamp (timestamp) RETURNS float8 AS 'SELECT date_part(\'epoch\', $1) AS RESULT;' LANGUAGE 'sql';

      • [deleted]

      • Post #3

      Why don't you just store the date as a unix-timestamp ie: integer?

        Hi all,
        I wanted to thank Tom and Vincent for their quick replies. I'm actually quite a newbie, so I'm going to have to go poke around Tom's site for a while and figure out how his solution works (I haven't even begun using user-defined PHP functions yet).

        Actually, Vincent, I came up with the same solution shortly after posting the message. It just never occurred to me that the Unix timestamp (the number of seconds since a certain date) would be used for displaying dates, since calculations would be involved. It seemed like storing a count of seconds would be most useful for determining intervals. But sure enough, it's the INT4 timestamp that PHP wants for the date() function.

        Thanks a lot for helping with what surely won't be my last question,
        Chris McCormick
        www.the-athenaeum.org

          I haven't even begun using user-defined PHP functions yet

          My solution is a user defined function in postgresql, not php, FYI.

            Write a Reply...