Thread: how to add seconds to a TimestampTz
Is there a better way than going to time_t and back? I am currently using this: db->next_worker = time_t_to_timestamptz(timestamptz_to_time_t(current_time) + autovacuum_naptime); (db->next_worker is a TimestampTz, as is current_time. autovacuum_naptime is integer for a number of seconds) but it doesn't seem clean, and the comments in the functions more or less say that their use is discouraged. I saw about doing it via the interval input/output but that's an awful lot of work ... Is this the first time this is going to be done in the backend? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Is there a better way than going to time_t and back?
Isn't the standard SQL-level locutiontimestamptz + numeric_value * '1 second'::interval
? I'm not sure what would be the most convenient realization
of this at the C level, but please stay away from time_t ...
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > Is there a better way than going to time_t and back? > > Isn't the standard SQL-level locution > timestamptz + numeric_value * '1 second'::interval > ? I'm not sure what would be the most convenient realization > of this at the C level, but please stay away from time_t ... Yes, it is, but we would have to be calling interval input, interval-times-integer, then interval-plus-timestamp, each time we scheduled a worker (to calculate the "time of next worker start"), which sounds like too much work ... an idea would be to store the result of interval input and multiplication, and recalculate each time we got SIGHUP. But then, maybe this is premature optimization on my part. I'll write it in the natural form for now, and then we'll see if we should rewrite it; and then, maybe it's easier to write something equivalent to TimestampDifference ... -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Mar 15, 2007, at 5:58 AM, Tom Lane wrote: > > ? I'm not sure what would be the most convenient realization > of this at the C level, but please stay away from time_t ... what's wrong with time_t ? Does postgres has some sort of "time" API, that can be used instead? -- Grzegorz Jaskiewicz C/C++ freelance for hire
If you read the autovacuum_naptime into an Interval object once, why can't you just use timestamptz_pl_interval ? You won't be using the interval input/output repeatedly surely. Regards Sailesh -- Sailesh Krishnamurthy Amalgamated Insight [W] (650) 242-3503 [C] (650) 804-6585 -----Original Message----- From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Alvaro Herrera Sent: Wednesday, March 14, 2007 3:46 PM To: Hackers Subject: [HACKERS] how to add seconds to a TimestampTz Is there a better way than going to time_t and back? I am currently using this: db->next_worker = time_t_to_timestamptz(timestamptz_to_time_t(current_time) + autovacuum_naptime); (db->next_worker is a TimestampTz, as is current_time. autovacuum_naptime is integer for a number of seconds) but it doesn't seem clean, and the comments in the functions more or less say that their use is discouraged. I saw about doing it via the interval input/output but that's an awful lot of work ... Is this the first time this is going to be done in the backend? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
Grzegorz Jaskiewicz wrote: > > On Mar 15, 2007, at 5:58 AM, Tom Lane wrote: > > > > >? I'm not sure what would be the most convenient realization > >of this at the C level, but please stay away from time_t ... > > what's wrong with time_t ? Does postgres has some sort of "time" API, > that can be used instead? Sure. See TimestampTz (as mentioned in the subject). -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Is there a better way than going to time_t and back? I am currently > using this: > > db->next_worker = > time_t_to_timestamptz(timestamptz_to_time_t(current_time) + > autovacuum_naptime); > > (db->next_worker is a TimestampTz, as is current_time. > autovacuum_naptime is integer for a number of seconds) For the archives, I just discovered on timestamp.h this macro to help: avdb->ad_next_worker = TimestampTzPlusMilliseconds(now, naptime_secs * 1000); This is a lot simpler and faster ... -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support