Re: [GENERAL] Timestamp index not being hit

Andreas Terrius writes: > --Query 2, Does not hit index > SELECT * FROM idxtbl > where ( current_timestamp is null or btime < current_timestamp) > AND ( current_timestamp - INTERVAL '7 DAYS' is null or btime > > current_timestamp - INTERVAL '7 DAYS') > --Query 4, Hit Index > SELECT * FROM idxtbl

[GENERAL] Timestamp index not being hit

Hello Ive been running into an issue with postgresql not hitting index on select queries. Below is the sql query I used to test my issue. CREATE TABLE idxtbl ( id BIGINT, aint BIGINT, btime TIMESTAMPTZ, ctext TEXT, dbool BOOLEAN, PRIMARY KEY(id) ); --Inserted 10 mil random data /* TEST TIMESTAMP

Re: [GENERAL] timestamp without timezone to have timezone

On 11/06/2016 09:24 AM, Benjamin Adams wrote: Please Reply to list also. I have Cced list > On Nov 6, 2016 11:07 AM, "Adrian Klaver" > wrote: >> >> On 11/06/2016 06:11 AM, Benjamin Adams wrote: >> > I have a server that has a column timestamp without timezone. >

Re: [GENERAL] timestamp without timezone to have timezone

On Sun, Nov 6, 2016 at 7:10 AM, Melvin Davidson wrote: > > > > On Sun, Nov 6, 2016 at 9:11 AM, Benjamin Adams wrote: >> >> I have a server that has a column timestamp without timezone. >> >> Is the time still saved? >> if I select column with timestamp it will show server timestamp with timezone.

Re: [GENERAL] timestamp without timezone to have timezone

On 11/06/2016 06:11 AM, Benjamin Adams wrote: > I have a server that has a column timestamp without timezone. > > Is the time still saved? Yes the timestamp is always saved. What that timestamp is differs: test[5432]=# create table ts_tsz_test(fld_1 timestamp, fld_2 timestamp with time zone);

Re: [GENERAL] timestamp without timezone to have timezone

On Sun, Nov 6, 2016 at 9:11 AM, Benjamin Adams wrote: > I have a server that has a column timestamp without timezone. > > Is the time still saved? > if I select column with timestamp it will show server timestamp with > timezone. > > But If I move the data from EST to Central will the timestamp w

[GENERAL] timestamp without timezone to have timezone

I have a server that has a column timestamp without timezone. Is the time still saved? if I select column with timestamp it will show server timestamp with timezone. But If I move the data from EST to Central will the timestamp with timezone be correct? Or will it just not make the adjustment? T

Re: [GENERAL] Timestamp with timezone output

Ok, it seems the Daylight Savings Time is perfect explanation to me. Thanks y'all, you're the best ! *--* *Arnaud Becher* Paris - San Francisco 10 rue du Faubourg Poissonnière - 75010 Paris T. + 33 (0)6 17 15 52 43 http://www.inovia.fr <

Re: [GENERAL] Timestamp with timezone output

On Wed, May 25, 2016 at 9:47 AM, Arnaud Inovia Team wrote: > While using "psql", when selecting a column timestamp with > timezone, I get results with different timezones: > > -[ RECORD 6 ]---+--- > expiration_date | 2015-09-07 00:00:00+02 > -[ RECORD 7 ]---+--

Re: [GENERAL] Timestamp with timezone output

Hi, Le 25 mai 2016 4:50 PM, "Arnaud Inovia Team" a écrit : > > I have some question regarding some timezone behaviour. > > Documentation is saying: > > > When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in t

Re: [GENERAL] Timestamp with timezone output

On 2016-05-25 09:47, Arnaud Inovia Team wrote: > I have some question regarding some timezone behaviour. > > Documentation is saying: >> When a timestamp with time zone value is output, it is always converted from >> UTC to the current timezone zone, and displayed as local time in that zone.

[GENERAL] Timestamp with timezone output

I have some question regarding some timezone behaviour. Documentation is saying: > When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone

Re: [GENERAL] timestamp check

On 07/14/2015 07:13 AM, Ramesh T wrote: i added .MS getting values,But Problem query keep on running but not displaying results,when i add like limit 5.it is return values.. what is the problem with query..? As has been explained several times already, subtracting one timestamp

Re: [GENERAL] timestamp check

i added .MS getting values,But Problem query keep on running but not displaying results,when i add like limit 5.it is return values.. what is the problem with query..? changed date and changed_dttimezone are are parameters.. select to_char((current_timestamp - TO_TIMESTAMP(to_char(chaged_date,'

Re: [GENERAL] timestamp check

Yes,But i need to display last digits also [image: Inline image 1] like 1500 08-09-10.738901 On Mon, Jul 13, 2015 at 8:18 PM, Francisco Olarte wrote: > Hi Ramesh: > > On Sun, Jul 12, 2015 at 8:21 AM, Ramesh T > wrote: > >> postgres query >> select current_timestamp- >> TO_TIMESTAMP(to_char(DA

Re: [GENERAL] timestamp check

On 11/07/2015 17:11, Ramesh T wrote: > okay,i'm executing a query from pgadmin3. > > i want display time with timezone.But above query displaying date and > time not timezone... > > On Sat, Jul 11, 2015 at 9:34 PM, David G. Johnston > mailto:david.g.johns...@gmail.com>> wrote: > > On Fri, J

Re: [GENERAL] timestamp check

On 07/11/2015 11:21 PM, Ramesh T wrote: postgres query select current_timestamp- TO_TIMESTAMP(to_char(DATE1, '-MM-DD HH24'|| ' '||'MI'||' '||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names WHERE name=DATETIMEZOZE1) , ''-MM-DD HH24'||' '||'MI'||' '||'SS')::timestamptz

Re: [GENERAL] timestamp check

any help..? On Sun, Jul 12, 2015 at 11:51 AM, Ramesh T wrote: > postgres query > select current_timestamp- > TO_TIMESTAMP(to_char(DATE1, '-MM-DD HH24'|| ' '||'MI'||' '||'SS')||' > '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names > WHERE name=DATETIMEZOZE1) , ''-MM-DD HH24'||' '

Re: [GENERAL] timestamp check

okay,i'm executing a query from pgadmin3. i want display time with timezone.But above query displaying date and time not timezone... On Sat, Jul 11, 2015 at 9:34 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Jul 10, 2015 at 8:54 AM, Ramesh T > wrote: > >> >> select curre

Re: [GENERAL] timestamp check

postgres query select current_timestamp- TO_TIMESTAMP(to_char(DATE1, '-MM-DD HH24'|| ' '||'MI'||' '||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names WHERE name=DATETIMEZOZE1) , ''-MM-DD HH24'||' '||'MI'||' '||'SS')::timestamptz getting result.. [image: Inline image 1]

Re: [GENERAL] timestamp check

On 07/10/2015 05:54 AM, Ramesh T wrote: select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names WHERE name='US/Eastern'),'-MM-DD HH24'||':'||'MI'||':'||'SS')::timestamptz; it's not displa

Re: [GENERAL] timestamp check

On 10/07/2015 13:54, Ramesh T wrote: > > select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD > HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM > pg_catalog.pg_timezone_names > WHERE name='US/Eastern'),'-MM-DD > HH24'||':'||'MI'||':'||'SS')::timestamptz; > > it

Re: [GENERAL] timestamp check

On 07/10/2015 05:54 AM, Ramesh T wrote: select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names WHERE name='US/Eastern'),'-MM-DD HH24'||':'||'MI'||':'||'SS')::timestamptz; it's not displa

Re: [GENERAL] timestamp check

On Fri, Jul 10, 2015 at 8:54 AM, Ramesh T wrote: > > select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD > HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM > pg_catalog.pg_timezone_names > WHERE name='US/Eastern'),'-MM-DD > HH24'||':'||'MI'||':'||'SS')::timestamp

[GENERAL] timestamp check

select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names WHERE name='US/Eastern'),'-MM-DD HH24'||':'||'MI'||':'||'SS')::timestamptz; it's not displaying timezone..any help..?

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On 10/02/2013 02:53 PM, Tim Uckun wrote: I do think it would help to add it to the docs. The name "TIMESTAMP WITH TIME ZONE" clearly implies the time zone is stored in the field. One day there will really be a timestamp with time zone embedded in it field and I wonder what they will call that.

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

I do think it would help to add it to the docs. The name "TIMESTAMP WITH TIME ZONE" clearly implies the time zone is stored in the field. One day there will really be a timestamp with time zone embedded in it field and I wonder what they will call that.

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 10:40:36AM -0700, Steve Crawford wrote: > >>>The reason for that is that in PostgreSQL there is no time zone > >>information stored along with a "timestamp with time zone", > >>it is stored in UTC. > >A better name might perhaps been "timezone aware timestamp". > > > >Karst

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On 10/02/2013 01:49 AM, Tim Uckun wrote: >The reason for that is that in PostgreSQL there is no time zone information stored along with a "timestamp with time zone", it is stored in UTC. That seems unintuitive. What is the difference between timestamp without time zone and timestamp with time z

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On 10/02/2013 04:19 AM, Karsten Hilbert wrote: On Wed, Oct 02, 2013 at 09:49:38PM +1300, Tim Uckun wrote: The reason for that is that in PostgreSQL there is no time zone information stored along with a "timestamp with time zone", it is stored in UTC. A better name might perhaps been "timezone

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On 10/02/2013 05:58 AM, Karsten Hilbert wrote: On Wed, Oct 02, 2013 at 11:48:02AM +, Albe Laurenz wrote: Or does that still need some C sprinkling (for operator support, say) ? Exactly. If you want "<" to work right for this data type that's the road you have to go. I see. Whatever bec

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 02:09:23PM +, Albe Laurenz wrote: > Karsten Hilbert wrote: > > Whatever became of the 2011 intent to implement > > the above that's linked to in the blog post ? > > You'd have to ask Alvaro. I figured he'd maybe read this on-list :-) Karsten -- GPG key ID E4071346 @

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

Karsten Hilbert wrote: > Whatever became of the 2011 intent to implement > the above that's linked to in the blog post ? You'd have to ask Alvaro. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresq

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 11:48:02AM +, Albe Laurenz wrote: >> Or does that still need some C sprinkling (for operator support, say) ? > > Exactly. If you want "<" to work right for this data type > that's the road you have to go. I see. Whatever became of the 2011 intent to implement the abo

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

Karsten Hilbert wrote: > > > Maybe the question I need to ask is "how can I store the time zone along > > > with the timestamp" > > > > Store an additional field "offset". > > If you want to invest more energy and don't mind writing C, > > you could create your own data type. > > Might not a comp

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 09:18:30AM +, Albe Laurenz wrote: > > Maybe the question I need to ask is "how can I store the time zone along > > with the timestamp" > > Store an additional field "offset". > If you want to invest more energy and don't mind writing C, > you could create your own dat

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 09:49:38PM +1300, Tim Uckun wrote: > >The reason for that is that in PostgreSQL there is no time zone > information stored along with a "timestamp with time zone", > it is stored in UTC. A better name might perhaps been "timezone aware timestamp". Karsten -- GPG key ID E

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On Wed, Oct 02, 2013 at 09:49:38PM +1300, Tim Uckun wrote: > Maybe the question I need to ask is "how can I store the time zone along > with the timestamp" You need an extra field, say, of type interval. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

Tim Uckun wrote: >> The reason for that is that in PostgreSQL there is no time zone >> information stored along with a "timestamp with time zone", >> it is stored in UTC. > > That seems unintuitive. What is the difference between timestamp without time > zone and timestamp with > time zone? I was

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

That's interesting article but it tells me that I can't really use the timestamp with time zone data type. I really need to store the time zone information along with the datetime and do not want to automatically convert the timestamp to the connection time zone. If one row has a timestamp in UT

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

On 02/10/2013, at 6:49 PM, Tim Uckun wrote: > >The reason for that is that in PostgreSQL there is no time zone > information stored along with a "timestamp with time zone", > it is stored in UTC. > > That seems unintuitive. What is the difference between timestamp without time > zone and times

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

>The reason for that is that in PostgreSQL there is no time zone information stored along with a "timestamp with time zone", it is stored in UTC. That seems unintuitive. What is the difference between timestamp without time zone and timestamp with time zone? I was expecting to have the time zone s

Re: [GENERAL] Timestamp with and without timezone conversion confusion.

Tim Uckun wrote: > I have the following query. [...] > SELECT > interval_start, > (interval_start AT TIME ZONE 'Africa/Monrovia')::timestamp with time zone as > interval_start_in_africa, > min_datetime, > min_datetime AT TIME ZONE 'Europe/Berlin' as min_datetime_in_berlin, > max_datetime,

[GENERAL] Timestamp with and without timezone conversion confusion.

I have the following query. with parsed_data as ( SELECT devicereportedtime , DATE_TRUNC('minute', devicereportedtime - (EXTRACT(minute FROM devicereportedtime)::integer % 5 || 'minutes')::interval) as interval_start FROM systemevents WHERE devicereportedtime >=

Re: [GENERAL] timestamp with timezone and time zone name

On 08/07/2012 08:36 PM, Shridhar Daithankar wrote: On Tuesday 07 Aug 2012 12:21:04 AM Tom Lane wrote: > Shridhar Daithankar writes: > > I am wondering, why following two values result in a shift by 3.5 hours. I > > would expect them to be identical. > > > > I understand that canonical ti

Re: [GENERAL] timestamp with timezone and time zone name

On Tuesday 07 Aug 2012 12:21:04 AM Tom Lane wrote: > Shridhar Daithankar writes: > > I am wondering, why following two values result in a shift by 3.5 hours. I > > would expect them to be identical. > > > > I understand that canonical time zone names could be ambiguous at times > > but I think IS

Re: [GENERAL] timestamp with timezone and time zone name

Shridhar Daithankar writes: > I am wondering, why following two values result in a shift by 3.5 hours. I > would expect them to be identical. > I understand that canonical time zone names could be ambiguous at times but I > think IST is not one of them. I don't know why you'd think that ... sr

[GENERAL] timestamp with timezone and time zone name

Hello, I am wondering, why following two values result in a shift by 3.5 hours. I would expect them to be identical. I understand that canonical time zone names could be ambiguous at times but I think IST is not one of them. Any explanation? --- test=# select '2012-08-07 05:24:56.

Re: [GENERAL] Timestamp with time zone 'negative' problem

"Prodan, Andrei" writes: > I have a DB in which items which are 'always valid' have a from_date of > 19000101 00+1 (Europe/Berlin) > When i try to restore the same DB to (Europe/Bucharest), instead of > 19000101 00+2, the timestamp becomes "1900-01-01 00:44:24+01:44:24" > which is ... stra

[GENERAL] Timestamp with time zone 'negative' problem

Hello, I have a DB in which items which are 'always valid' have a from_date of 19000101 00+1 (Europe/Berlin) When i try to restore the same DB to (Europe/Bucharest), instead of 19000101 00+2, the timestamp becomes "1900-01-01 00:44:24+01:44:24" which is ... strange. My software then compl

Re: [GENERAL] Timestamp parsing with blanked time part

Ireneusz Pluta writes: > [ Postgres accepts timestamp input of the form '2011-07-22 :' ] > Some other datetime parsers reject it, the Perl DateTime::Format::Pg is an > example. > Is this case a subject of eventual corrections in the future versions of > postgres and it would start emit errors t

[GENERAL] Timestamp parsing with blanked time part

Hi, consider the following: select quote_literal(blank_hms) as "quote_literal(blank_hms)", blank_hms::timestamp as "blank_hms::timestamp" from (select unnest(array['2011-07-22 :', '2011-07-22 : ', '2011-07-22 : : ']::text[]) as blank_hms) a; select version(); quote_literal(blank_hms) | blan

[GENERAL] Timestamp parsing with blanked time part

Hi, consider the following: select quote_literal(blank_hms) as "quote_literal(blank_hms)", blank_hms::timestamp as "blank_hms::timestamp" from (select unnest(array['2011-07-22 :', '2011-07-22 : ', '2011-07-22 : : ']::text[]) as blank_hms) a; select version(); quote_literal(blank_hms) | blan

Re: [GENERAL] timestamp(0) vs. timestamp

On 27.04.2011 19:36, Tom Lane wrote: Erwin Brandstetter writes: Hi all! This may seem unimportant, but I still would like to know. I have columns for timestamps without fractional digits, so I could define them as timestamp(0). However, there is no way fractions could ever enter anyway, because

Re: [GENERAL] timestamp(0) vs. timestamp

Erwin Brandstetter writes: > Hi all! > This may seem unimportant, but I still would like to know. > I have columns for timestamps without fractional digits, so I could > define them as timestamp(0). > However, there is no way fractions could ever enter anyway, because > triggers and / or checks g

[GENERAL] timestamp(0) vs. timestamp

Hi all! This may seem unimportant, but I still would like to know. I have columns for timestamps without fractional digits, so I could define them as timestamp(0). However, there is no way fractions could ever enter anyway, because triggers and / or checks guarantee values without fractional seco

Re: [GENERAL] timestamp configuration

ise we all die. Read 'Hot, Flat, and Crowded' Laugh at http://www.yert.com/film.php --- On Mon, 5/24/10, Osvaldo Kussama wrote: > From: Osvaldo Kussama > Subject: Re: [GENERAL] timestamp configuration > To: "Dennis Gearon" > Cc: pgsql-general@postgresql.org >

Re: [GENERAL] timestamp configuration

2010/5/25 Dennis Gearon : > is there anyway with a running instance of postgres to find out if it was > compiled with: > >   BIGINT > vs >   DOUBLE PRECISION > > timestamps? > SHOW INTEGER_DATETIMES; Osvaldo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

[GENERAL] timestamp configuration

is there anyway with a running instance of postgres to find out if it was compiled with: BIGINT vs DOUBLE PRECISION timestamps? Dennis Gearon Signature Warning EARTH has a Right To Life, otherwise we all die. Read 'Hot, Flat, and Crowded' Laugh at http://www.yert.com

Re: [GENERAL] timestamp convert to date

Thank you very much for the quick answer ! I'm considering installing the upcoming 9.0 beta instead of 8.4. Will it be available as an installable ubuntu 9.04 package ? I have not tried to install a beta release before so I'm a bit worried about crashing my 8.3.10 install which works now. Is there

Re: [GENERAL] timestamp convert to date

Dan S writes: > I did a test but it looks like date doesn't support infinity as a value. Try 8.4 or later. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pg

[GENERAL] timestamp convert to date

Hi List ! I'm running "PostgreSQL 8.3.10 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu 4.3.3-5ubuntu4) 4.3.3" (Ubuntu 9.04) I want to use -infinity,infinity as my date interval maximum endpoints in an application I'm writing . Is it possible to use date ? I did a test but it looks li

Re: [GENERAL] timestamp literal out of line

Tom Lane wrote on 07.03.2010 16:34: We wouldn't even support it at all because it's so syntactically messy and inextensible I like it :) It's the only cross-DBMS way to write down a date or timestamp literal. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To m

Re: [GENERAL] timestamp literal out of line

Martijn van Oosterhout writes: > postgres=# prepare test1 as select timestamp '2009-01-01'; > PREPARE > postgres=# prepare test2 as select timestamp $1; > ERROR: syntax error at or near "$1" > The workaround is simple, use a cast instead, but is there a particular > reason why you can't use a pa

[GENERAL] timestamp literal out of line

Version: 8.3.9 I was surprised when I came across the following while changing some code to send parameters out of line (to avoid interpolation). postgres=# prepare test1 as select timestamp '2009-01-01'; PREPARE postgres=# prepare test2 as select timestamp $1; ERROR: syntax error at or near "$1

Re: [GENERAL] timestamp fields and order by?

Steve Wampler writes: > Tom Lane wrote: >> Even so, though, I think it would be quite foolish to design an >> application around the assumption that the timestamps of successive >> insertions will be distinguishable. Put in a serial column. > I'll do that. I was a bit surprised to see that the

Re: [GENERAL] timestamp fields and order by?

Tom Lane wrote: Your example looks like what's being called is current_timestamp(3), or else something on the client side is rounding it off to 3 digits. The bare function will give whatever resolution the operating system supplies, down to microseconds at best (the limit of the POSIX API for thi

Re: [GENERAL] timestamp fields and order by?

Steve Wampler writes: > It appears as though the timestamp resolution is now low > enough that it cannot keep up with the speed at which > items can be inserted. Your example looks like what's being called is current_timestamp(3), or else something on the client side is rounding it off to 3 digit

[GENERAL] timestamp fields and order by?

It appears as though the timestamp resolution is now low enough that it cannot keep up with the speed at which items can be inserted. That is, when ordering entries by timestamp, it's possible that the ordering will not reflect the actual entry order. (I assume the corollary is that the sort us

[GENERAL] timestamp without time zone and datetime

Hi, I have a stored procedure in db that takes a 'timestamp without time zone' as its parameter. The application uses c# and npgsql to access database. When I call the stored procedure from c#, I got an exception says that the stored procedure with 'timestamp with time zone' is unknown. It seems

Re: [GENERAL] timestamp with time zone, retrieving input offset/timezone

On 4 Aug 2009, at 13:09, Der Tung wrote: But know I have two Users A and B in different timezones. When A saves a timestamp I want B to: - Get the timestamp displayed in his timezone - Get the timestamp displayed in the timezone a originally saved it in Does the type “time

Re: [GENERAL] timestamp with time zone, retrieving input offset/timezone

On Tue, Aug 4, 2009 at 5:09 AM, Der Tung wrote: > Hello there, > > We are creating an Application that needs to handle timestamps in different > timezones in particular: > > Input and output in timezone of the User is no problem with > > Set Time Zone and At Time Zone > > But know I have two Users

[GENERAL] timestamp with time zone, retrieving input offset/timezone

Hello there, We are creating an Application that needs to handle timestamps in different timezones in particular: Input and output in timezone of the User is no problem with Set Time Zone and At Time Zone But know I have two Users A and B in different timezones. When A saves a timest

Re: [GENERAL] timestamp with time zone tutorial

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 serve

Re: [GENERAL] timestamp with time zone tutorial

> 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/g

Re: [GENERAL] timestamp with time zone tutorial

the movie - 'Syriana' --- On Sun, 7/19/09, Tom Lane wrote: > From: Tom Lane > Subject: Re: [GENERAL] timestamp with time zone tutorial > To: "Dennis Gearon" > Cc: pgsql-general@postgresql.org > Date: Sunday, July 19, 2009, 8:00 PM > Dennis Gearon > writes: &

Re: [GENERAL] timestamp with time zone tutorial

Dennis Gearon 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, st

Re: [GENERAL] timestamp with time zone tutorial

raphical location of the SUBMITTER. > From: Adrian Klaver > 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

Re: [GENERAL] timestamp with time zone tutorial

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 offs

Re: [GENERAL] timestamp with time zone tutorial

rom: Adrian Klaver > Subject: Re: [GENERAL] timestamp with time zone tutorial > To: "Dennis Gearon" > 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

Re: [GENERAL] timestamp with time zone tutorial

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/functio

Re: [GENERAL] timestamp with time zone tutorial

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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subsc

Re: [GENERAL] timestamp with time zone tutorial

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 be

Re: [GENERAL] 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 t

[GENERAL] 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) Po

[SOLVED] Re: [GENERAL] Timestamp indicies not being used!

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 First of all *thank* you very much to all that replied! :) Sam's suggestion actually did the trick! I created the multi-column index and the stalling went away. Yay! Best regards, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Tom L

Re: [GENERAL] Timestamp indicies not being used!

Andreas Kretschmer writes: > Sam Mason wrote: >> Based on your query, I think you want a multi-column index---probably on >> (id,zulu_timestamp). >> >> The problem with just having an index on either column is that it's >> difficult to combine them and PG hence just thinks that it will be > Sin

Re: [GENERAL] Timestamp indicies not being used!

On Sun, Jul 19, 2009 at 02:56:04PM +0200, Andreas Kretschmer wrote: > Sam Mason wrote: > > The problem with just having an index on either column is that it's > > difficult to combine them and PG hence just thinks that it will be > > Since 8.1 PG can do an bitmap index scan using both indexes...

Re: [GENERAL] Timestamp indicies not being used!

Sam Mason wrote: > On Sun, Jul 19, 2009 at 11:15:39AM +0100, Pedro Doria Meunier wrote: > > I have a table ~18M rows with a 'timestamp with time zone' column. It's > > indexed thus: > > > > CREATE INDEX my_table_timestamp_idx > > ON my_table > > USING btree > > (zulu_timestamp); > > Based

Re: [GENERAL] Timestamp indicies not being used!

On Sun, Jul 19, 2009 at 11:15:39AM +0100, Pedro Doria Meunier wrote: > I have a table ~18M rows with a 'timestamp with time zone' column. It's > indexed thus: > > CREATE INDEX my_table_timestamp_idx > ON my_table > USING btree > (zulu_timestamp); Based on your query, I think you want a mult

Re: [GENERAL] Timestamp indicies not being used!

Pedro Doria Meunier wrote: > Hi All, > > I *really* need some help with this one... > > I have a table ~18M rows with a 'timestamp with time zone' column. It's > indexed thus: > > CREATE INDEX my_table_timestamp_idx > ON my_table > USING btree > (zulu_timestamp); > > whenever I issue a

[GENERAL] Timestamp indicies not being used!

Hi All, I *really* need some help with this one... I have a table ~18M rows with a 'timestamp with time zone' column. It's indexed thus: CREATE INDEX my_table_timestamp_idx ON my_table USING btree (zulu_timestamp); whenever I issue a command like: SELECT speed, digital_input_1, digital_in

Re: [GENERAL] timestamp no fractional seconds

Grzegorz Jaśkiewicz wrote: what difference does the (0) make than ? is timestamp() a function than ?/ The (0) is setting the precision. Telling it to store 0 places for the fractional second. Much like setting scale and precision with numeric(6,2) Scott -- Sent via pgsql-general mailing list

Re: [GENERAL] timestamp no fractional seconds

what difference does the (0) make than ? is timestamp() a function than ?/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] timestamp no fractional seconds

=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= writes: > what difference does the (0) make than ? is timestamp() a function than ?/ No, it's a type. See http://www.postgresql.org/docs/8.3/static/datatype-datetime.html regards, tom lane -- Sent via pgsql-general mailing list (pgs

Re: [GENERAL] timestamp no fractional seconds

Brandon Metcalf writes: > I suppose my question really boils down to how do I cast the timestamp > with no fractional seconds part? For example, if I have a table where > I haven't put a limit on the fractional seconds part, how would I > select the timestamp without fractional seconds? Cast to

Re: [GENERAL] timestamp no fractional seconds

p == pavel.steh...@gmail.com writes: p> Hello p> use timestamp(0) p> timestamp[0] means array of timestamps Of course. I was reading the documentation wrong and taking the [] as literal instead of it meaning an optional parameter as it always does. Thanks. -- Brandon -- Sent via pgsql

Re: [GENERAL] timestamp no fractional seconds

2009/6/2 Brandon Metcalf : > b == bran...@geronimoalloys.com writes: > >  b> I need to create a table with two columns of type timestamp but I >  b> don't want to store any fractional part of the seconds field.  So, >  b> I created a table with: > >  b>   CREATE TABLE timeclock ( >  b>       timecl

Re: [GENERAL] timestamp no fractional seconds

b == bran...@geronimoalloys.com writes: b> I need to create a table with two columns of type timestamp but I b> don't want to store any fractional part of the seconds field. So, b> I created a table with: b> CREATE TABLE timeclock ( b> timeclock_id SERIAL, b> employee_id I

Re: [GENERAL] timestamp no fractional seconds

Hello use timestamp(0) timestamp[0] means array of timestamps regards Pavel Stehule 2009/6/2 Brandon Metcalf : > I need to create a table with two columns of type timestamp but I > don't want to store any fractional part of the seconds field.  So, > I created a table with: > >  CREATE TABLE tim

  1   2   3   >