Thread: what is the date format in binary query results
What can I expect for a date format from a PGresult containing binary results? Specifically the Oid type is TIMESTAMPTZOID. In this case what does the PQgetvalue actually return? What does the char* point to?
Thanks.
- samantha
Samantha Atkins <sjatkins@mac.com> writes:
> What can I expect for a date format from a PGresult containing binary
> results? Specifically the Oid type is TIMESTAMPTZOID.
It's either an int8 representing microseconds away from 2000-01-01
00:00:00 UTC, or a float8 representing seconds away from the same
origin.
regards, tom lane
Il Thursday 08 November 2007 16:18:58 Tom Lane ha scritto: > It's either an int8 representing microseconds away from 2000-01-01 > 00:00:00 UTC, or a float8 representing seconds away from the same > origin. Does this mean that negative numbers are for timestamps before y2k? Why and when there is a choice between int8 and float8 representation? -- Reg me Please
Il Thursday 08 November 2007 17:09:22 Tom Lane ha scritto: > Reg Me Please <regmeplease@gmail.com> writes: > > Il Thursday 08 November 2007 16:18:58 Tom Lane ha scritto: > >> It's either an int8 representing microseconds away from 2000-01-01 > >> 00:00:00 UTC, or a float8 representing seconds away from the same > >> origin. > > > > Does this mean that negative numbers are for timestamps before y2k? > > Right. > > > Why and when there is a choice between int8 and float8 representation? > > configure --enable-integer-datetimes. Wow: it's at compile time! How can I tell which one has been choosen by my distribution (Ubuntu)? -- Reg me Please
Reg Me Please <regmeplease@gmail.com> writes:
> Il Thursday 08 November 2007 16:18:58 Tom Lane ha scritto:
>> It's either an int8 representing microseconds away from 2000-01-01
>> 00:00:00 UTC, or a float8 representing seconds away from the same
>> origin.
> Does this mean that negative numbers are for timestamps before y2k?
Right.
> Why and when there is a choice between int8 and float8 representation?
configure --enable-integer-datetimes.
regards, tom lane
Reg Me Please <regmeplease@gmail.com> writes:
> Il Thursday 08 November 2007 17:09:22 Tom Lane ha scritto:
>> configure --enable-integer-datetimes.
> How can I tell which one has been choosen by my distribution (Ubuntu)?
"show integer_datetimes". For programmatic purposes, try
PQparameterStatus(pgconn, "integer_datetimes").
regards, tom lane
Reg Me Please wrote: > Il Thursday 08 November 2007 16:18:58 Tom Lane ha scritto: > > It's either an int8 representing microseconds away from 2000-01-01 > > 00:00:00 UTC, or a float8 representing seconds away from the same > > origin. > > Does this mean that negative numbers are for timestamps before y2k? Yes. > Why and when there is a choice between int8 and float8 representation? At compilation time, by the --enable-integer-datetimes flag to configure. You can find out whether the server you're currently connected to uses integer datetimes with SHOW integer_datetimes. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "The only difference is that Saddam would kill you on private, where the Americans will kill you in public" (Mohammad Saleh, 39, a building contractor)
This brings up a second question. How should I do byte order conversion for 8 byte ints? I can't use hton ntoh routines as they max out at 32 bits. Is there a better way? Also, are floating point numbers guaranteed uniform? If any one knows a a clean code example of binary binding of parameters and binary extraction of results for all major types in C against lippq that would be a very useful thing to have in the standard docs. - samantha On Nov 8, 2007, at 7:18 AM, Tom Lane wrote: > Samantha Atkins <sjatkins@mac.com> writes: >> What can I expect for a date format from a PGresult containing binary >> results? Specifically the Oid type is TIMESTAMPTZOID. > > It's either an int8 representing microseconds away from 2000-01-01 > 00:00:00 UTC, or a float8 representing seconds away from the same > origin. > > regards, tom lane - samantha Vote Ron Paul for President in 2008 -- Save Our Constitution! Go to RonPaul2008.com, and search "Ron Paul" on YouTube
How can it be a simple 8 byte int or float and specify a timezone? This is only a time interval from a fixed date/time. Where is the timezone part? On Nov 8, 2007, at 7:18 AM, Tom Lane wrote: > Samantha Atkins <sjatkins@mac.com> writes: >> What can I expect for a date format from a PGresult containing binary >> results? Specifically the Oid type is TIMESTAMPTZOID. > > It's either an int8 representing microseconds away from 2000-01-01 > 00:00:00 UTC, or a float8 representing seconds away from the same > origin. > > regards, tom lane - samantha Vote Ron Paul for President in 2008 -- Save Our Constitution! Go to RonPaul2008.com, and search "Ron Paul" on YouTube
Samantha Atkins <sjatkins@mac.com> writes:
> This brings up a second question. How should I do byte order
> conversion for 8 byte ints? I can't use hton ntoh routines as they
> max out at 32 bits. Is there a better way?
Well, there's the PDP-endianness of odious memory, but AFAIK all current
platforms are internally consistent about the ordering of smaller and
larger pieces. Look at the float8 and int64 send/recv routines in our
current sources.
> Also, are floating point numbers guaranteed uniform?
No :-( ... although there are darn few machines anymore that don't
at least claim to follow the IEEE 754 spec.
regards, tom lane
Samantha Atkins <sjatkins@mac.com> writes:
> How can it be a simple 8 byte int or float and specify a timezone?
It doesn't. Read the thread again.
regards, tom lane
Less than useful. I did read the thread last night. What am I missing? On Dec 12, 2007, at 12:06 AM, Tom Lane wrote: > Samantha Atkins <sjatkins@mac.com> writes: >> How can it be a simple 8 byte int or float and specify a timezone? > > It doesn't. Read the thread again. > > regards, tom lane - samantha Vote Ron Paul for President in 2008 -- Save Our Constitution! Go to RonPaul2008.com, and search "Ron Paul" on YouTube
OK, I read it again. I don't see anything about how the timezone is specified for this type of column. On Dec 12, 2007, at 12:06 AM, Tom Lane wrote: > Samantha Atkins <sjatkins@mac.com> writes: >> How can it be a simple 8 byte int or float and specify a timezone? > > It doesn't. Read the thread again. > > regards, tom lane - samantha Vote Ron Paul for President in 2008 -- Save Our Constitution! Go to RonPaul2008.com, and search "Ron Paul" on YouTube
Samantha Atkins wrote: > OK, I read it again. I don't see anything about how the timezone is > specified for this type of column. It is not -- that's the point. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 "La tristeza es un muro entre dos jardines" (Khalil Gibran)
Samantha Atkins wrote: > OK, I read it again. I don't see anything about how the timezone is > specified for this type of column. What differs between timestamp and timestamptz is the behavior on input and output, but in both cases what is effectively stored is only an UTC timestamp, no timezone. That's unlike what the name of the type, "timestamp with time zone", seems to imply. One would expect that that would represent a certain value of timestamp along with a certain value of time zone, but that's not the case. Personally I think it's a rather unfortunate choice of name, but maybe that comes straight from the SQL standard. Oracle has a similar datatype and they call it "timestamp with local time zone", I guess it's a bit less confusing, as least it gives a hint that there is a local-related thing to be aware of. Anyway see http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html especially chapter 8.5.1.3 ! -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Dec 12, 2007 2:14 AM, Samantha Atkins <sjatkins@mac.com> wrote: > This brings up a second question. How should I do byte order > conversion for 8 byte ints? I can't use hton ntoh routines as they > max out at 32 bits. Is there a better way? Also, are floating point > numbers guaranteed uniform? > > If any one knows a a clean code example of binary binding of > parameters and binary extraction of results for all major types in C > against lippq that would be a very useful thing to have in the > standard docs. We are working on a patch (not necessarily to be migrated with the source code) to allow simplified binding of binary types to native C types. You can see an older version here: it has examples how to read off a lot of the types in binary. We should have a new patch in a day or two that should demonstrate how to read the rest of the types in binary. We also handled 64 bit ints... the patch is here http://archives.postgresql.org/pgsql-patches/2007-12/msg00014.php if you are patient we can provide examples for all the basic built in types, including possibly arrays... merlin p.s don't top post, but I dig Ron Paul :-)
Merlin Moncure wrote: > On Dec 12, 2007 2:14 AM, Samantha Atkins <sjatkins@mac.com> wrote: >> This brings up a second question. How should I do byte order >> conversion for 8 byte ints? I can't use hton ntoh routines as they >> max out at 32 bits. Is there a better way? Also, are floating point >> numbers guaranteed uniform? >> >> If any one knows a a clean code example of binary binding of >> parameters and binary extraction of results for all major types in C >> against lippq that would be a very useful thing to have in the >> standard docs. > > We are working on a patch (not necessarily to be migrated with the > source code) to allow simplified binding of binary types to native C > types. You can see an older version here: it has examples how to read > off a lot of the types in binary. We should have a new patch in a day > or two that should demonstrate how to read the rest of the types in > binary. We also handled 64 bit ints... > > the patch is here > http://archives.postgresql.org/pgsql-patches/2007-12/msg00014.php > > if you are patient we can provide examples for all the basic built in > types, including possibly arrays... > > merlin > > p.s don't top post, but I dig Ron Paul :-) > > This is from the patch merlin mentioned. static unsigned int *swap8(void *outp, void *inp, int tonet) { static int n = 1; unsigned int *in = (unsigned int *)inp; unsigned int *out = (unsigned int *)outp; /* swap when needed */ if(*(char *)&n == 1) { out[0] = (unsigned int)(tonet ? htonl(in[1]) : ntohl(in[1])); out[1] = (unsigned int)(tonet ? htonl(in[0]) : ntohl(in[0])); } else { out[0] = in[0]; out[1] = in[1]; } return out; } // example if(PQfformat(res, field_num) == 1) { long long n; swap8(&n, PQgetvalue(res, 0, 0), 0); printf("%lld\n", n); } It will works with doubles as well. andrew
On Wed, 2007-12-12 at 10:16 -0800, Samantha Atkins wrote: > OK, I read it again. I don't see anything about how the timezone is > specified for this type of column. I went to the manual instead, see below for a useful section. Since the "internally stored value is always in UTC", it doesn't need to store a timezone in there. the internal timezone is always UTC. When you read the contents of the timestamp with timezone column, it converts it for you, either to the current timezone or a timezone you specify. ====> For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone. 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 or use the AT TIME ZONE construct (see Section 9.9.3). 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 zone reference can be specified for the conversion using AT TIME ZONE. http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html#DATATYPE-DATETIME-INTERNALS <=====
Merlin Moncure wrote: > On Dec 12, 2007 2:14 AM, Samantha Atkins <sjatkins@mac.com> wrote: >> This brings up a second question. How should I do byte order >> conversion for 8 byte ints? I can't use hton ntoh routines as they >> max out at 32 bits. Is there a better way? Also, are floating point >> numbers guaranteed uniform? >> >> If any one knows a a clean code example of binary binding of >> parameters and binary extraction of results for all major types in C >> against lippq that would be a very useful thing to have in the >> standard docs. > > We are working on a patch (not necessarily to be migrated with the > source code) to allow simplified binding of binary types to native C > types. You can see an older version here: it has examples how to read > off a lot of the types in binary. We should have a new patch in a day > or two that should demonstrate how to read the rest of the types in > binary. We also handled 64 bit ints... > > the patch is here > http://archives.postgresql.org/pgsql-patches/2007-12/msg00014.php > > if you are patient we can provide examples for all the basic built in > types, including possibly arrays... > > merlin > > p.s don't top post, but I dig Ron Paul :-) > > This is from the patch merlin mentioned. static unsigned int *swap8(void *outp, void *inp, int tonet) { static int n = 1; unsigned int *in = (unsigned int *)inp; unsigned int *out = (unsigned int *)outp; /* swap when needed */ if(*(char *)&n == 1) { out[0] = (unsigned int)(tonet ? htonl(in[1]) : ntohl(in[1])); out[1] = (unsigned int)(tonet ? htonl(in[0]) : ntohl(in[0])); } else { out[0] = in[0]; out[1] = in[1]; } return out; } // example if(PQfformat(res, field_num) == 1) { long long n; swap8(&n, PQgetvalue(res, 0, 0), 0); printf("%lld\n", n); } andrew