Thread: timestamp with time zone tutorial
INSERTing timestampz, 'to_timestamp', output formatting, input formatting, SERVER TIME, USER_LOCAL_TIME, multi timezone
applications.
Anyone wonder how those all work? **I** sure do!!!
Is there a tutorial anywhere on how to do all those? Can anyone who is knowledgeable about this:
(1) Post a good description.
(2) Post a link to a good description.
(3) Give input to me so that I can write a good tutorial to post on
the postgres site?
TIA,
Dennis Gearon
Signature Warning
----------------
EARTH has a Right To Life
I agree with Bolivian President Evo Morales
# The right to life: "The right for no ecosystem to be eliminated by the irresponsible acts of human beings."
# The right of biosystems to regenerate themselves: "Development cannot be infinite. There's a limit on everything."
# The right to a clean life: "The right for Mother Earth to live without contamination, pollution. Fish and animals and
treeshave rights."
# The right to harmony and balance between everyone and everything: "We are all interdependent."
See the movie - 'Inconvenient Truth'
See the movie - 'Syriana'
On Sunday 19 July 2009 1:29:14 pm Dennis Gearon wrote: > INSERTing timestampz, 'to_timestamp', output formatting, input formatting, > SERVER TIME, USER_LOCAL_TIME, multi timezone applications. > > Anyone wonder how those all work? **I** sure do!!! > > Is there a tutorial anywhere on how to do all those? Can anyone who is > knowledgeable about this: > > (1) Post a good description. > (2) Post a link to a good description. > (3) Give input to me so that I can write a good tutorial to post on > the postgres site? > > TIA, > Dennis Gearon > As far as I can tell it is covered here: http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html -- Adrian Klaver aklaver@comcast.net
On Sun, Jul 19, 2009 at 01:29:14PM -0700, Dennis Gearon wrote: > INSERTing timestampz, 'to_timestamp', output formatting, input formatting, SERVER TIME, USER_LOCAL_TIME, multi timezoneapplications. > > Anyone wonder how those all work? **I** sure do!!! The official docs[1,2] have generally been enough for me, could you be more specific about what is causing trouble? That said, I've never written code that needs to be more than trivially aware of timezones before. I'm not sure if this is just because the abstractions provided are nice or if I've not had to solve a hard problem here. > (3) Give input to me so that I can write a good tutorial to post on > the postgres site? There's already a page on the postgres wiki about this[3], maybe something needs clarifying? -- Sam http://samason.me.uk/ [1] http://www.postgresql.org/docs/current/static/datatype-datetime.html [2] http://www.postgresql.org/docs/current/static/functions-datetime.html [3] http://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL
I read it better, and it makes more sense now.
But,
I'd like it to show how to insert:
'strings' - which it does
timestampz value -->using to_timestampz(...)
integers::timestampz
On Sunday 19 July 2009 4:56:09 pm Dennis Gearon wrote: > I read it better, and it makes more sense now. > > But, > I'd like it to show how to insert: > 'strings' - which it does > timestampz value -->using to_timestampz(...) For above: http://www.postgresql.org/docs/8.4/interactive/functions-formatting.html > integers::timestampz See above or: http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html In particular see 9.9.1. EXTRACT, date_part epoch For date and timestamp values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for interval values, the total number of seconds in the interval SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08'); Result: 982384720 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); Result: 442800 Here is how you can convert an epoch value back to a time stamp: SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second'; -- Adrian Klaver aklaver@comcast.net
None of the examples of converting a string to_timestamp() show using a time zone input as an input.Does it allow full lengthtimezones for daylight savings time at the timestamp instant in time, or just an abbreviation for a fixed offset? --- On Sun, 7/19/09, Adrian Klaver <aklaver@comcast.net> wrote: > From: Adrian Klaver <aklaver@comcast.net> > Subject: Re: [GENERAL] timestamp with time zone tutorial > To: "Dennis Gearon" <gearond@sbcglobal.net> > Cc: pgsql-general@postgresql.org > Date: Sunday, July 19, 2009, 5:15 PM > On Sunday 19 July 2009 4:56:09 pm > Dennis Gearon wrote: > > I read it better, and it makes more sense now. > > > > But, > > I'd like it to show how to insert: > > 'strings' - which it does > > timestampz value -->using > to_timestampz(...) > > For above: > http://www.postgresql.org/docs/8.4/interactive/functions-formatting.html > > > integers::timestampz > > See above or: > http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html > In particular see > 9.9.1. EXTRACT, date_part > > epoch > > For date and timestamp values, the number of > seconds since 1970-01-01 > 00:00:00 UTC (can be negative); for interval values, the > total number of > seconds in the interval > > SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME > ZONE '2001-02-16 > 20:38:40-08'); > Result: 982384720 > > SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 > hours'); > Result: 442800 > > Here is how you can convert an epoch value > back to a time stamp: > > SELECT TIMESTAMP WITH TIME ZONE 'epoch' + > 982384720 * INTERVAL '1 second'; > > > > -- > Adrian Klaver > aklaver@comcast.net >
On Sunday 19 July 2009 6:41:24 pm Dennis Gearon wrote: > None of the examples of converting a string to_timestamp() show using a > time zone input as an input.Does it allow full length timezones for > daylight savings time at the timestamp instant in time, or just an > abbreviation for a fixed offset? > > This might be easier if you could give an example of what you are trying to accomplish :) -- Adrian Klaver aklaver@comcast.net
Good Idea Adrian!
What I want is to be able to insert into my project's database, times given by anybody anywhere on the planet (the
SUBMITTER),add the appropriate timezone in the insert statement so that it in 'GMT/UMT' neutral'. I believe this is the
wayPostgres does it, storing times in GMT time.
When I pull it out for anyone on the planet (for the QUERRIER), by their geographic location or address, I give
theSELECT statement the timezone value of the QUERRIER and adjust it to the location of the QUERRIER also supplying
themthe time in the geographical location of the SUBMITTER.
> From: Adrian Klaver <aklaver@comcast.net>
<snip>
> Subject: Re: [GENERAL] timestamp with time zone tutorial
Dennis Gearon wrote:
> > None of the examples of converting a string
> to_timestamp() show using a
> > time zone input as an input.Does it allow full length
> timezones for
> > daylight savings time at the timestamp instant in
> time, or just an
> > abbreviation for a fixed offset?
> >
> >
>
> This might be easier if you could give an example of what
> you are trying to
> accomplish :)
>
>
> --
> Adrian Klaver
> aklaver@comcast.net
>
Dennis Gearon <gearond@sbcglobal.net> writes:
> What I want is to be able to insert into my project's database, times given by anybody anywhere on the planet
(theSUBMITTER), add the appropriate timezone in the insert statement so that it in 'GMT/UMT' neutral'. I believe this
isthe way Postgres does it, storing times in GMT time.
So just do it. What do you need to_timestamp for? The basic
timestamptz input function is far more likely to get it right
for input that's not in a precise predetermined format.
regards, tom lane
Hey Tom,
I was trying to use 'US/Pacific-New' as my long, unabbreviated timezone and it wasn't working. I thought postgres
wasn'taccepting the unabbreviated, geopolitical, daylight savings time, time zones. Turns out, the server that I was
on,(not my own box), didn't have that in the '/usr/share/zoneinfo/US' directory. My bad.
I just have to read more on how to get it out relative to a different time zone than it went in. I'll find it.
Dennis Gearon
Signature Warning
----------------
EARTH has a Right To Life
I agree with Bolivian President Evo Morales
# The right to life: "The right for no ecosystem to be eliminated by the irresponsible acts of human beings."
# The right of biosystems to regenerate themselves: "Development cannot be infinite. There's a limit on everything."
# The right to a clean life: "The right for Mother Earth to live without contamination, pollution. Fish and animals and
treeshave rights."
# The right to harmony and balance between everyone and everything: "We are all interdependent."
See the movie - 'Inconvenient Truth'
See the movie - 'Syriana'
--- On Sun, 7/19/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [GENERAL] timestamp with time zone tutorial
> To: "Dennis Gearon" <gearond@sbcglobal.net>
> Cc: pgsql-general@postgresql.org
> Date: Sunday, July 19, 2009, 8:00 PM
> Dennis Gearon <gearond@sbcglobal.net>
> writes:
> > What I want is to be able to
> insert into my project's database, times given by anybody
> anywhere on the planet (the SUBMITTER), add the appropriate
> timezone in the insert statement so that it in 'GMT/UMT'
> neutral'. I believe this is the way Postgres does it,
> storing times in GMT time.
>
> So just do it. What do you need to_timestamp
> for? The basic
> timestamptz input function is far more likely to get it
> right
> for input that's not in a precise predetermined format.
>
>
> regards, tom lane
>
> I just have to read more on how to get it out relative to a different > time zone than it went in. I'll find it. Sounds like a job for SELECT ... AT TIME ZONE ...; Karsten -- Neu: GMX Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate für nur 19,99 Euro/mtl.!* http://portal.gmx.net/de/go/dsl02
On Sunday 19 July 2009 10:59:24 pm Dennis Gearon wrote: > Hey Tom, > I was trying to use 'US/Pacific-New' as my long, unabbreviated > timezone and it wasn't working. I thought postgres wasn't accepting the > unabbreviated, geopolitical, daylight savings time, time zones. Turns out, > the server that I was on, (not my own box), didn't have that in the > '/usr/share/zoneinfo/US' directory. My bad. > > I just have to read more on how to get it out relative to a different > time zone than it went in. I'll find it. > > > Dennis Gearon > For me it easier to think of the time zones as a formatting option rather than a data storage attribute. The simple explanation is that it always goes in as UTC. The more complicated explanation follows. The tz data types are stored as UTC. The time zone information is used on input to make the correct offset from the specified tz to UTC. On output the procedure is reversed. Since the data is stored as UTC you get to choose whatever time zone you want it displayed as by using the appropriate setting or function. The issue is if it is important to know the tz that was used for the input offset. That would require a separate field. You can search the archives for the many discussions that have occurred on this point. -- Adrian Klaver aklaver@comcast.net