Re: [GENERAL] timestamp without timezone to have timezone

2016-11-06 Thread Adrian Klaver 
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.
>> >
>> > 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);
>> CREATE TABLE
>>
>> test[5432]=# insert into ts_tsz_test values (now(), now());
>> INSERT 0 1
>>
>> test[5432]=# select * from ts_tsz_test ;
>>fld_1| fld_2
>> +---
>>  2016-11-06 07:52:01.053218 | 2016-11-06 07:52:01.053218-08
>>
>>
>> As you can see the timestamp field is a naive value, it has no concept
> of timezone and
>> assumes local time. The timestamp with time zone is time zone aware,
> in this case displaying
>> as my local time also with the appropriate offset. The reason being
> that timestamp with time
>> zone is stored as UTC and converted on display. To learn more see the
> below:
>>
>> https://www.postgresql.org/docs/9.5/static/datatype-datetime.html
>> 8.5.1.3. Time Stamps
>>
>> > if I select column with timestamp it will show server timestamp with
>> > timezone.
>>
>> Correct.
>>
>> >
>> > But If I move the data from EST to Central will the timestamp with
>> > timezone be correct?
>>
>> Are you moving the data or the server or both?
> 
> Just moving data. Server will have new local time.

If I am following correctly you will be changing the TimeZone setting from EST 
to Central, correct?

So for practical purposes both, in that the moved data will have naive 
timestamp data in 
a 'new' timezone from its point of view

> 
>>
>> In other words can you be more specific about what moving the data means?
>>
>> If you are not moving the server(eg retaining the TimeZome config)
> then the timestamp(w/o tz)
>> will be displaying in EST not Central. Postgres has no internal way of
> knowing
>> what the timestamp(w/o tz) data values are anchored to. This leads to
> another question.
>>
>> Did all the current values originate from EST?
> 
> All current are est.  If I do select at UTC. Will data respond with same
> time after moving data?

Well first are the values actually all derived from EST or are they a mix of 
EST/EDT? I suspect the latter.

As to your question, maybe:

https://www.postgresql.org/docs/9.5/static/datatype-datetime.html#DATATYPE-TIMEZONES

8.5.1.3. Time Stamps

"Conversions between timestamp without time zone and timestamp with time zone 
normally assume 
that the timestamp without time zone value should be taken or given as timezone 
local time. 
A different time zone can be specified for the conversion using AT TIME ZONE."

To continue my previous example and given that this morning was the DST --> 
Standard Time transition. Also
that I am in Pacific time zones:

test[5432]=# insert into ts_tsz_test values ('2016-11-05 07:52:01.053218' , 
'2016-11-05 07:52:01.053218');
INSERT 0 1
test[5432]=# select * from ts_tsz_test ;
   fld_1| fld_2 
+---
 2016-11-06 07:52:01.053218 | 2016-11-06 07:52:01.053218-08
 2016-11-05 07:52:01.053218 | 2016-11-05 07:52:01.053218-07


test[5432]=# select fld_1, fld_1 at time zone 'utc', fld_2, fld_2 at time zone 
'utc' from ts_tsz_test;   
   fld_1|   timezone| fld_2 
|  timezone  
+---+---+
 2016-11-06 07:52:01.053218 | 2016-11-06 00:52:01.053218-07 | 2016-11-06 
07:52:01.053218-08 | 2016-11-06 15:52:01.053218
 2016-11-05 07:52:01.053218 | 2016-11-05 00:52:01.053218-07 | 2016-11-05 
07:52:01.053218-07 | 2016-11-05 14:52:01.053218

test[5432]=# select fld_1, fld_1 at time zone 'America/Los_Angeles', fld_2, 
fld_2 at time zone 'utc' from ts_tsz_test;
   fld_1|   timezone| fld_2 
|  timezone  
+---+---+
 2016-11-06 07:52:01.053218 | 2016-11-06 07:52:01.053218-08 | 2016-11-06 
07:52:01.053218-08 | 2016-11-06 15:52:01.053218
 2016-11-05 07:52:01.053218 | 2016-11-05 07:52:01.053218-07 | 2016-11-05 
07:52:01.053218-07 | 2016-11-05 14:52:01.053218


test[5432]=# select fld_1, fld_1 at time zone 'America/Los_Angeles' at time 
zone 'UTC', fld_2, fld_2 at time zone 'utc' from ts_tsz_test;
   fld_1|  timezone  | fld_2
 |

Re: [GENERAL] timestamp without timezone to have timezone

2016-11-06 Thread Steve Crawford 
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.
>>
>> 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?
>>
>> Thanks
>> Ben
>
>
> >But If I move the data from EST to Central will the timestamp with
timezone be correct?
>
> The correct way to do that is to use the  AT TIME ZONE function.
>
>
https://www.postgresql.org/docs/9.4/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
>
> eg:
>
> postgres=> SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'EST' as
Eastern,
> postgres->TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'CST' as
Central,
> postgres->TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST' as
Mountain,
> postgres->TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'PST' as
Pacific;
> eastern |central |mountain
 |pacific
>
+++
>  2001-02-16 20:38:40-05 | 2001-02-16 21:38:40-05 | 2001-02-16 22:38:40-05
| 2001-02-16 23:38:40-05
> (1 row)


The question does not completely make sense and Melvin's answer is headed
in the right direction but there are a number of subtleties and
complications.

First, let me rephrase to make sure I understand the question. You have
some table(s) with column(s) of type timestamp without time zone. You
currently view the data from the perspective of US/Eastern (probably not
actually EST - more comments on the difference between offsets and zones
below) and want to know what happens if you view it from the perspective of
US/Central.

The short answer is that nothing will change. I'm in US/Pacific by default:

steve=> create temporary table foo (bar timestamp without time zone);
CREATE TABLE
steve=> insert into foo values (now());
INSERT 0 1

steve=> select bar from foo;
   bar

2016-11-06 08:57:06.808096

steve=> set timezone to 'US/Eastern';

steve=> select bar from foo;
   bar

2016-11-06 08:57:06.808096

steve=> set timezone to 'US/Central';

steve=> select bar from foo;
   bar

2016-11-06 08:57:06.808096


But if you do any sort of operation for which the timestamp alone is not
sufficient thus time zone information is required, PostgreSQL will
convert/calculate based on the current time zone setting.

Starting back at Pacific time, if I look at various other time zones I get:
steve=> select
   bar at time zone 'US/Pacific',
   bar at time zone 'US/Central',
   bar at time zone 'US/Eastern'
from
   foo;
-[ RECORD 1 ]---
timezone | 2016-11-06 08:57:06.808096-08
timezone | 2016-11-06 06:57:06.808096-08
timezone | 2016-11-06 05:57:06.808096-08

But if my client is set to Eastern I get:

steve=> set time zone 'US/Eastern';

steve=> select
   bar at time zone 'US/Pacific',
   bar at time zone 'US/Central',
   bar at time zone 'US/Eastern'
from
   foo;
-[ RECORD 1 ]---
timezone | 2016-11-06 11:57:06.808096-05
timezone | 2016-11-06 09:57:06.808096-05
timezone | 2016-11-06 08:57:06.808096-05

If you alter the table and change the data type to timestamp with time zone
(which to my mind is a bad name that we are stuck with - it should be
thought of as a "point in time" that can be displayed in any local time
zone) the data will be converted as above based on the current time zone
setting.

Another "gotcha": "EST" is an *offset* from UTC - specifically, it is
5-hours behind UTC. "US/Eastern", or one of the equivalent full names for
that zone (select * from pg_timezone_names;), is a time *zone*. Time zones
incorporate the various spring-forward/fall-back offset rules as they have
changed through history. Today is a convenient day for demonstrating. I
changed the table to have two columns, bar1 and bar2. bar2 has the
timestamp we used before and bar1 is the same timestamp but a day earlier.
This is what you will get if you display the values and the difference
between them in a zone-unaware way:

steve=> select bar1, bar2, bar2-bar1 from foo;
-[ RECORD 1 ]
bar1 | 2016-11-05 08:57:06.808096
bar2 | 2016-11-06 08:57:06.808096
?column? | 1 day

Now, let's change the data types (I'm still set to US/Eastern):

steve=> alter table foo alter column bar1 type timestamp with time zone;

steve=> alter table foo alter column bar2 type timestamp with time zone;

steve=> select bar1, bar2, bar2-bar1 from foo;
-[ RECORD 1 ]---
bar1 | 2016-11-05 08:57:06.808096-04
bar2 | 2016-11-06 08:57:06.808096-05
?column? | 1 day 01:00:00

Note

Re: [GENERAL] timestamp without timezone to have timezone

2016-11-06 Thread Adrian Klaver 
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); 
 
CREATE TABLE

 
test[5432]=# insert into ts_tsz_test values (now(), now());
INSERT 0 1

test[5432]=# select * from ts_tsz_test ;
   fld_1| fld_2 
+---
 2016-11-06 07:52:01.053218 | 2016-11-06 07:52:01.053218-08


As you can see the timestamp field is a naive value, it has no concept of 
timezone and 
assumes local time. The timestamp with time zone is time zone aware, in this 
case displaying
as my local time also with the appropriate offset. The reason being that 
timestamp with time
zone is stored as UTC and converted on display. To learn more see the below:

https://www.postgresql.org/docs/9.5/static/datatype-datetime.html
8.5.1.3. Time Stamps

> if I select column with timestamp it will show server timestamp with
> timezone.

Correct.

> 
> But If I move the data from EST to Central will the timestamp with
> timezone be correct?

Are you moving the data or the server or both?

In other words can you be more specific about what moving the data means?

If you are not moving the server(eg retaining the TimeZome config) then the 
timestamp(w/o tz) 
will be displaying in EST not Central. Postgres has no internal way of knowing
what the timestamp(w/o tz) data values are anchored to. This leads to another 
question.

Did all the current values originate from EST?

> Or will it just not make the adjustment?

See above. 
> 
> Thanks
> Ben


-- 
Adrian Klaver
adrian.kla...@aklaver.com


-- 
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 without timezone to have timezone

2016-11-06 Thread Melvin Davidson 
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 with
> timezone be correct?
> Or will it just not make the adjustment?
>
> Thanks
> Ben
>







*>But If I move the data from EST to Central will the timestamp with
timezone be correct?The correct way to do that is to use the  AT TIME ZONE
function.https://www.postgresql.org/docs/9.4/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
*

*eg:*








*postgres=> SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'EST'
as Eastern,postgres->TIMESTAMP '2001-02-16 20:38:40' AT TIME
ZONE 'CST' as Central,postgres->TIMESTAMP '2001-02-16
20:38:40' AT TIME ZONE 'MST' as Mountain,postgres->TIMESTAMP
'2001-02-16 20:38:40' AT TIME ZONE 'PST' as Pacific;eastern
 |central |mountain|
pacific+++
2001-02-16 20:38:40-05 | 2001-02-16 21:38:40-05 | 2001-02-16
22:38:40-05 | 2001-02-16 23:38:40-05(1 row)
*

*-- *
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] timestamp without timezone to have timezone

2016-11-06 Thread Benjamin Adams 
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?

Thanks
Ben

[GENERAL] Timestamp without timezone

2007-11-20 Thread T.J. Adami 
I'm using Microsoft Visual Foxpro 9 developing an ERP application,
using PostgreSQL 8.2.5 and ODBC connection in version 7.

If I upgrade my ODBC drivers to use PostgreSQL ANSI, becomes an
error like this:

function saldo_estoque(unknown, unknown, unknown, unknown,
timestamp without time zone) does not exist

However, the unknown types are BPCHAR on function saldo_estoque,
and timestamp without time zone is DATE type.

How can I solve this?

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Timestamp without timezone

2007-11-20 Thread Reg Me Please 
Il Tuesday 20 November 2007 15:01:53 T.J. Adami ha scritto:
 I'm using Microsoft Visual Foxpro 9 developing an ERP application,
 using PostgreSQL 8.2.5 and ODBC connection in version 7.

 If I upgrade my ODBC drivers to use PostgreSQL ANSI, becomes an
 error like this:

 function saldo_estoque(unknown, unknown, unknown, unknown,
 timestamp without time zone) does not exist

 However, the unknown types are BPCHAR on function saldo_estoque,
 and timestamp without time zone is DATE type.

 How can I solve this?

 ---(end of broadcast)---
 TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

It's very likely that you have to do some explici casting on all other
paramter types, as the only one the DB has recognised is the last one,
aka TIMESTAMPTZ.

-- 
Reg me Please
Non quietis maribus nauta

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Timestamp without timezone

2007-11-20 Thread T.J. Adami 
On 20 nov, 12:35, [EMAIL PROTECTED] (Reg Me Please) wrote:
 Il Tuesday 20 November 2007 15:01:53 T.J. Adami ha scritto:



  I'm using Microsoft Visual Foxpro 9 developing an ERP application,
  using PostgreSQL 8.2.5 and ODBC connection in version 7.

  If I upgrade my ODBC drivers to use PostgreSQL ANSI, becomes an
  error like this:

  function saldo_estoque(unknown, unknown, unknown, unknown,
  timestamp without time zone) does not exist

  However, the unknown types are BPCHAR on function saldo_estoque,
  and timestamp without time zone is DATE type.

  How can I solve this?

  ---(end of broadcast)---
  TIP 4: Have you searched our list archives?

 http://archives.postgresql.org/

 It's very likely that you have to do some explici casting on all other
 paramter types, as the only one the DB has recognised is the last one,
 aka TIMESTAMPTZ.

 --
 Reg me Please
 Non quietis maribus nauta

 ---(end of broadcast)---
 TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

It appears to be a VFP handling error, once I use parametrized calls
with memvars. This means that VFP will create SQL statement for me
translating variables into SQL values (something like
PreparedStatement on Java).
It's quite simple to make a explicit cast, but due to the size of
application, this will be not worth. ODBC driver version 7 is working
perfectly, but it's very old and I'm afraid of get some errors.
Assuming that PostgreSQL will increase it's versions (now on 8.3 beta
2), there is a need to upgrade the Windows ODBC driver to version 8?

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match