Thread: timestamp with time zone tutorial

timestamp with time zone tutorial

From
Dennis Gearon
Date:
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'

Re: timestamp with time zone tutorial

From
Adrian Klaver
Date:
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

Re: timestamp with time zone tutorial

From
Sam Mason
Date:
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

Re: timestamp with time zone tutorial

From
Dennis Gearon
Date:
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


Re: timestamp with time zone tutorial

From
Adrian Klaver
Date:
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

Re: timestamp with time zone tutorial

From
Dennis Gearon
Date:
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
>

Re: timestamp with time zone tutorial

From
Adrian Klaver
Date:
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

Re: timestamp with time zone tutorial

From
Dennis Gearon
Date:
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
>

Re: timestamp with time zone tutorial

From
Tom Lane
Date:
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

Re: timestamp with time zone tutorial

From
Dennis Gearon
Date:
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
>

Re: timestamp with time zone tutorial

From
"Karsten Hilbert"
Date:
>      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

Re: timestamp with time zone tutorial

From
Adrian Klaver
Date:
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