timestamp with time zone tutorial

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 timezone applications.

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]http://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL, 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 length timezones 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:

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 way Postgres 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 the SELECT statement the timezone value of the QUERRIER and adjust it to the location of the QUERRIER also supplying them the 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:

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

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

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 trees have 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:

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