Timestamp to time_t
in [PgSql]
|
From: "Kevin Grittner" on 15 Sep 2009 14:49 Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > [ shrug... ] We *have* that property, for sane cases such as > adding and subtracting a fixed number of days. Adding and subtracting months is very common in business software. I have seen application bugs related to this many times. I suspect that such bugs would occur less often with a more abstract date type and a date normalization strategy for mapping to the calendar than it does with typical techniques; but it's not something I would propose that PostgreSQL move toward. (Well, maybe some day as a pgfoundry project or something, given that such a system could plug right in, but not as the default date handling -- for compatibility, if nothing else.) I was just reacting to the assertion that date abstraction was such a stupid thing to do that nothing else proposed in a document which supports it is worth considering. The Turing Award isn't usually awarded to those proposing complete nonsense. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "Kevin Grittner" on 15 Sep 2009 15:06 Andrew Gierth <andrew(a)tao11.riddles.org.uk> wrote: > (To me, the fact that the spec's idea of 2009-01-31 + 1 month > corresponds to a value that current_date will never be equal to is > a far greater show-stopper.) You get to pick which way you want to normalize that to the calendar -- 31 days past the start of the next month, or pulled back to the last day of the next month which is not greater than 31. The latter is more common, but I've seen both practices in real world business applications. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "David E. Wheeler" on 15 Sep 2009 16:16 On Sep 15, 2009, at 11:01 AM, Andrew Gierth wrote: > If you want to store both a timestamp and an associated timezone you > can do > it right now, using a composite type or two columns, with the > advantage that > you get semantics that you can rely on. How would a composite work in practice? Can you index it on the timestamp? Or would you have to use two columns for that? I could see a real advantage to a type that stored the TZ with which it was created, with the ability to fetch it back out. Internally the data could be stored just like it is with timestamptz, and by default, perhaps, it would display in $PGTZ, but if $PGTZ was set to a value like "original" or something, it should display the originals. Now *that* would be really useful IMHO. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Scott Mohekey on 15 Sep 2009 19:56
On Wed, Sep 16, 2009 at 2:23 AM, Kevin Grittner <Kevin.Grittner(a)wicourts.gov > wrote: > Scott Mohekey <scott.mohekey(a)telogis.com> wrote: > > I think the issue is that we treat TIMESTAMP WITHOUT TIME ZONE as > > TIMESTAMP at GMT. We then convert it to a users local timezone > > within application code. > > TIMESTAMP WITHOUT TIME ZONE is stored "raw" and is not considered to > be associated to a time zone until you do so. It will default to > assigning the time zone set on your server, which is normally your > local time zone. Unless that's GMT, you will need to be very > careful to always localize the timestamp to GMT before doing > anything with it. > > -Kevin > This is exactly what we do. All of our servers are set to GMT time, all times in the database are stored in GMT time, and are converted to a users local time within the application. Scott Mohekey Systems/Application Specialist OnTrack Telogis, Inc. www.telogis.com www.telogis.co.nz +1 949 625-4115 ext. 207 (USA) +64 3339 2825 x207 (NZ) Leading Global Platform for Location Based Services -- This e-mail, and any attachments, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. It is the property of Telogis. If you are not the intended recipient of this e-mail, you are hereby notified that any dissemination, distribution or copying of this e-mail, any attachments thereto, and use of the information contained, is strictly prohibited. If you have received this e-mail in error, please notify the sender and permanently delete the original and any copy there of. |