Thread: timestamp default values
Hi all, I have a table which is used for logging, and I want a timestamp column which reliably stores the insert time for each row inside a transaction, with maximum precision. Now, if I'm reading the documentation (http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT) correctly, the only way to get the current time inside a transaction is to use timeofday(). timeofday() returns text, and moreover it returns in a bizarre format which cannot be converted directly into any useful temporal types, at least not in 8.0.2: => select timeofday(); timeofday ------------------------------------- Sat Aug 06 14:41:49.596859 2005 EST (1 row) => select timeofday()::timestamp; ERROR: invalid input syntax for type timestamp: "Sat Aug 06 14:41:57.875478 2005 EST" => select timeofday()::date; ERROR: invalid input syntax for type date: "Sat Aug 06 14:43:41.672518 2005 EST" So, if I'm on the right track here, the only way to really get the value I want is to do something like: DEFAULT to_timestamp(timeofday(), 'Dy Mon DD HH24:MI:SS.US YYYY') Does this strike anybody else as circumlocutive? From the aforementioned manual page: It is important to know that CURRENT_TIMESTAMP and related functions return the start time of the current transaction; their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the "current" time, so that multiple modifications within the same transaction bear the same time stamp. timeofday() returns the wall-clock time and does advance during transactions. I agree that being able to reference the time the transaction started is a useful feature, but it should not be made available at the expense of being able to reference the actual time. Terms like "now" and "current timestamp" seem unambiguous to me -- they are misleading names for the transaction start time. At least, there should be a function that really does return the current timestamp. -- BJ
Brendan Jurd <direvus@gmail.com> writes:
> timeofday() returns text, and moreover it returns in a bizarre format
> which cannot be converted directly into any useful temporal types, at
> least not in 8.0.2:
Hm? Works fine for me. What datestyle setting do you have exactly?
regards, tom lane
On 8/6/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Brendan Jurd <direvus@gmail.com> writes: > > timeofday() returns text, and moreover it returns in a bizarre format > > which cannot be converted directly into any useful temporal types, at > > least not in 8.0.2: > > Hm? Works fine for me. What datestyle setting do you have exactly? > => show datestyle; DateStyle ----------- ISO, DMY
Brendan Jurd <direvus@gmail.com> writes:
> On 8/6/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Brendan Jurd <direvus@gmail.com> writes:
>>> timeofday() returns text, and moreover it returns in a bizarre format
>>> which cannot be converted directly into any useful temporal types, at
>>> least not in 8.0.2:
>>
>> Hm? Works fine for me. What datestyle setting do you have exactly?
> => show datestyle;
> DateStyle
> -----------
> ISO, DMY
Well, it works just fine here.
regression=# set datestyle = iso,dmy;
SET
regression=# select timeofday();
timeofday
-------------------------------------
Sat Aug 06 10:00:45.791921 2005 EDT
(1 row)
regression=# select timeofday()::timestamp;
timeofday
----------------------------
2005-08-06 10:00:47.920636
(1 row)
I'm testing 8.0 branch tip (or nearly so), not 8.0.2, but I don't see
any related bug fixes in the CVS logs. And this is something that's
always worked in the past --- else we'd have been more motivated to
change timeofday()'s behavior.
Is it possible you have a broken build? Any nondefault configure
options?
Can anyone else duplicate the problem?
regards, tom lane
On Sat, Aug 06, 2005 at 10:06:35AM -0400, Tom Lane wrote: > Can anyone else duplicate the problem? I couldn't duplicate the problem in 8.0.2 or in any other version from 7.2.8 through HEAD (latest CVS for all). Brendan, if you execute "\set VERBOSITY verbose" in psql and then generate the errors, what's the complete error message? Tom (or anybody else), could the errors could be due to the "if (VARSIZE(str) - VARHDRSZ > MAXDATELEN)" checks in text_date() and text_timestamp()? Could an encoding affect that? That's why I suggested increasing the verbosity: so we could see where the error is being raised. -- Michael Fuhr
On 8/7/05, Michael Fuhr <mike@fuhr.org> wrote:
> On Sat, Aug 06, 2005 at 10:06:35AM -0400, Tom Lane wrote:
> > Can anyone else duplicate the problem?
>
> I couldn't duplicate the problem in 8.0.2 or in any other version
> from 7.2.8 through HEAD (latest CVS for all).
>
> Brendan, if you execute "\set VERBOSITY verbose" in psql and then
> generate the errors, what's the complete error message?
>
> Tom (or anybody else), could the errors could be due to the "if
> (VARSIZE(str) - VARHDRSZ > MAXDATELEN)" checks in text_date() and
> text_timestamp()? Could an encoding affect that? That's why I
> suggested increasing the verbosity: so we could see where the error
> is being raised.
>
> --
> Michael Fuhr
>
Okay, I have some more information on this.
The error only occurs for some very particular outputs of timeofday().
Namely, Saturdays.
No I'm not kidding.
When I tried to diagnose the problem today (Sun Aug 07), everything worked fine.
I was able to replicate the error, however, by deliberately specifying
yesterday's timeofday() string:
=> select timeofday()::timestamp;
timeofday
----------------------------
2005-08-07 12:00:43.668919
(1 row)
=> select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp;
ERROR: 22007: invalid input syntax for type timestamp: "Sat Aug 06
12:00:43.668919 2005 EST"
LOCATION: DateTimeParseError, datetime.c:3333
=> select 'Sat Aug 13 12:00:43.668919 2005 EST'::timestamp;
ERROR: 22007: invalid input syntax for type timestamp: "Sat Aug 13
12:00:43.668919 2005 EST"
LOCATION: DateTimeParseError, datetime.c:3333
=> select 'Sat Aug 13 12:00:43.668919 2005 CST'::timestamp;
ERROR: 22007: invalid input syntax for type timestamp: "Sat Aug 13
12:00:43.668919 2005 CST"
LOCATION: DateTimeParseError, datetime.c:3333
=> select 'Sun Aug 07 12:00:43.668919 2005 EST'::timestamp;
timestamp
----------------------------
2005-08-07 12:00:43.668919
(1 row)
=> select 'Aug 06 12:00:43.668919 2005 EST'::timestamp;
timestamp
----------------------------
2005-08-06 12:00:43.668919
(1 row)
=> select 'Sat Aug 06 12:00:43.668919 2005'::timestamp;
timestamp
----------------------------
2005-08-06 12:00:43.668919
(1 row)
From the last few examples, you can see that the conversion succeeds
when "Sat" is present at the start, or when the timezone is present at
the end, but not when both are present, as in the timeofday() output.
Perhaps the parser is treating the string "Sat" as a timezone token?
--
BJ
On Sun, Aug 07, 2005 at 12:05:43PM +1000, Brendan Jurd wrote:
> => select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp;
> ERROR: 22007: invalid input syntax for type timestamp: "Sat Aug 06
> 12:00:43.668919 2005 EST"
What are your timezone and australian_timezones settings? I can
duplicate the problem thusly:
set australian_timezones to on;
set timezone to 'EST';
select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp;
ERROR: invalid input syntax for type timestamp: "Sat Aug 06 12:00:43.668919 2005 EST"
set australian_timezones to off;
select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp;
timestamp
----------------------------
2005-08-06 12:00:43.668919
(1 row)
--
Michael Fuhr
On 8/7/05, Michael Fuhr <mike@fuhr.org> wrote: > On Sun, Aug 07, 2005 at 12:05:43PM +1000, Brendan Jurd wrote: > > => select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp; > > ERROR: 22007: invalid input syntax for type timestamp: "Sat Aug 06 > > 12:00:43.668919 2005 EST" > > What are your timezone and australian_timezones settings? I can > duplicate the problem thusly: > > set australian_timezones to on; > set timezone to 'EST'; > select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp; > ERROR: invalid input syntax for type timestamp: "Sat Aug 06 12:00:43.668919 2005 EST" > > set australian_timezones to off; > select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp; > timestamp > ---------------------------- > 2005-08-06 12:00:43.668919 > (1 row) > > -- > Michael Fuhr > => show timezone; TimeZone --------------- Australia/ACT (1 row) => show australian_timezones; australian_timezones ---------------------- on (1 row) -- BJ
Here we go ... line 498 of backend/utils/adt/datetime.c:
/* Used for SET australian_timezones to override North American ones */
static datetkn australian_datetktbl[] = {
{"acst", TZ, POS(38)}, /* Cent. Australia */
{"cst", TZ, POS(42)}, /* Australia Central Std Time */
{"east", TZ, POS(40)}, /* East Australian Std Time */
{"est", TZ, POS(40)}, /* Australia Eastern Std Time */
{"sat", TZ, POS(38)},
};
"sat" is being parsed as a timezone. Probably "South Australian Time".
I'm guessing the parser is throwing an error because it thinks I'm
trying to give it two different timezones.
Are there any good reasons why the output of timeofday() needs to
include the three letter day-of-week? Could we either remove it, or
perhaps change it to the full day-of-week (e.g. "Saturday"), which
cannot be confused with a timezone?
Brendan Jurd <direvus@gmail.com> writes:
> Are there any good reasons why the output of timeofday() needs to
> include the three letter day-of-week?
If we are going to change it, I'd be inclined to make it output the
canonical ISO format (YYYY-MM-DD HH:MM:SS.SSSS-TZ). Or perhaps the
format should be the same as whatever the current DateStyle setting
would emit.
regards, tom lane
On 8/7/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Brendan Jurd <direvus@gmail.com> writes: > > Are there any good reasons why the output of timeofday() needs to > > include the three letter day-of-week? > > If we are going to change it, I'd be inclined to make it output the > canonical ISO format (YYYY-MM-DD HH:MM:SS.SSSS-TZ). Or perhaps the > format should be the same as whatever the current DateStyle setting > would emit. > > regards, tom lane > Going to ISO format would be a big improvement. Does anybody know why this function returns text? The documentation cites "historical reasons". Are any of those historical reasons still relevant? I would suggest that in the vast majority of cases, the desired type from timeofday() is timestamp. Wouldn't it make more sense to have it return timestamp, and then use to_char() for those cases where we want a textual representation of the time? Currently the function gets the time as a "pg_time_t", then converts it into a string with pg_strftime(). Then, in order to make the value useful we run that string through a datetime parser. I realise it's not a major performance hit, but it's just not elegant to run all these superfluous conversions. In the interests of backwards compatibility, how about I just write a new function that does the same thing as timeofday(), but returns timestamp? Or perhaps I could add an optional precision parameter to timenow(), so you could call timenow(6) and achieve the same thing. Objections? -- BJ
Brendan Jurd <direvus@gmail.com> writes:
> Does anybody know why this function returns text? The documentation
> cites "historical reasons". Are any of those historical reasons still
> relevant?
Backwards compatibility --- changing the result datatype would
surely break some applications. I'm a tad worried even about changing
the output format, but we can probably get away with that, especially
if we do it as part of a new release. (Back-patching such a change
seems like a no-no.)
There has been some talk of bypassing this problem by inventing a whole
new function with a different name, but no one's put up a proposal that
garnered enough support. (I seem to recall a tentative consensus on
inventing a single function that would take an argument to say whether
you wanted transaction start time, statement start time, or true current
time --- but that fell apart when it was pointed out that we would have
to label such a function volatile, thereby making it unindexable. We
really need to use differently-named functions for these things.)
> Or perhaps I could add an optional precision parameter to timenow(),
> so you could call timenow(6) and achieve the same thing.
I'd be interested to see how you do that, considering that abstime
can't store fractional seconds. timenow() is even more obsolete than
timeofday() --- AFAICS it's not documented at all, anywhere. The whole
abstime datatype is obsolete, actually, and will have to go away
sometime before 2038.
regards, tom lane
> I'd be interested to see how you do that, considering that abstime > can't store fractional seconds. timenow() is even more obsolete than > timeofday() --- AFAICS it's not documented at all, anywhere. The whole > abstime datatype is obsolete, actually, and will have to go away > sometime before 2038. > If the current implementation of timenow() is truly obsolete, would it be verboten to change its return type? We could rewrite the function to return timestamp, for example. -- BJ
Brendan Jurd <direvus@gmail.com> writes:
> If the current implementation of timenow() is truly obsolete, would it
> be verboten to change its return type? We could rewrite the function
> to return timestamp, for example.
[ shrug... ] This is just a variant of the choose-a-new-function-name
game. If we are going to choose a new function name, choosing one that
collides with an existing name (obsolete or not) doesn't seem like a
win to me. You could just as well choose another name, and avoid
angering whoever out there might still be using timenow().
BTW: at least with our current interpretation of these datatypes, the
only type that is sensible for a now()-like function to return is
timestamptz. Not plain timestamp; that cannot be considered to
represent a well-defined instant at all.
regards, tom lane
> [ shrug... ] This is just a variant of the choose-a-new-function-name > game. If we are going to choose a new function name, choosing one that > collides with an existing name (obsolete or not) doesn't seem like a > win to me. You could just as well choose another name, and avoid > angering whoever out there might still be using timenow(). Agreed. It looks like finding a good name for this function would in fact be the hardest part of adding it ... the namespace for now()-like functions is quite cluttered. I'd be inclined to go with "gettime()", but I'm certainly open to suggestions. > BTW: at least with our current interpretation of these datatypes, the > only type that is sensible for a now()-like function to return is > timestamptz. Not plain timestamp; that cannot be considered to > represent a well-defined instant at all. True.
On Sun, Aug 07, 2005 at 12:47:19AM -0400, Tom Lane wrote: > BTW: at least with our current interpretation of these datatypes, the > only type that is sensible for a now()-like function to return is > timestamptz. Not plain timestamp; that cannot be considered to > represent a well-defined instant at all. BTW, do we have a type which stores the TZ it originally had? -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "No hay hombre que no aspire a la plenitud, es decir, la suma de experiencias de que un hombre es capaz"
how do i get removed from list? tks __________________________________________________ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/