size of datatypes
A user on IDS 7.31 asked how many bytes INTEGER and DATETIME YEAR TO FRACTION(3) occupy, and where this is documented. Answers pointed to the "Guide to SQL: Reference" manual (data types chapter). Consensus: INTEGER is always 4 bytes in Informix, regardless of machine; DATETIME YEAR TO FRACTION(3) is 10 bytes. Jonathan Leffler explained why: it's stored like DECIMAL(17,3) — one byte for exponent/sign plus one byte per pair of digits (7 before, 2 after the decimal point), totalling 10.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all, My question is this: I am running 7.31.FD2X9 What is the size in bytes of the following 2 data types? integer datetime year to fraction(3) Follow-up: where can i find this information in the manuals? Thanks, Gary
At least for integer I think it depends on the machine you are running. J. 2008/10/27 GARY MARKWALTER <gary.markwalter@hp.com> > Hi all, > > My question is this: > I am running 7.31.FD2X9 > What is the size in bytes of the following 2 data types? > > integer > datetime year to fraction(3) > > Follow-up: where can i find this information in the manuals? > > Thanks, > Gary > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
SQL Ref manual chapter. Integer is 4. Datetime is probably 8. j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of GARY MARKWALTER Sent: Monday, October 27, 2008 6:22 AM To: ids@iiug.org Subject: size of datatypes [13790] Hi all, My question is this: I am running 7.31.FD2X9 What is the size in bytes of the following 2 data types? integer datetime year to fraction(3) Follow-up: where can i find this information in the manuals? Thanks, Gary **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
http://www-01.ibm.com/software/data/informix/pubs/library/ids_73.html Then look at: Guide to SQL: Reference, Version 7.3/8.2 (G251-1338-00) This guide provides information on the following topics: Informix databases, data types, system catalog tables, environment variables, and the stores7 demonstration database. It also contains a glossary. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of GARY MARKWALTER Sent: Monday, October 27, 2008 7:22 AM To: ids@iiug.org Subject: size of datatypes [13790] Hi all, My question is this: I am running 7.31.FD2X9 What is the size in bytes of the following 2 data types? integer datetime year to fraction(3) Follow-up: where can i find this information in the manuals? Thanks, Gary ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. UBS Financial Services Incorporated of Puerto Rico UBS AG
> > Follow-up: where can i find this information in the manuals? > Guide to SQL: Reference
Thank you all. I have got my answers. I love the quick responses!
By the way, my calculations came up with this. integer = 4 bytes Datetime year to fraction(3) = 10 bytes
On Mon, Oct 27, 2008 at 5:47 AM, Jean Sagi <jeansagi.ifx@gmail.com> wrote: > At least for integer I think it depends on the machine you are running. No, it does not. SQL INTEGER is always 4 bytes. > 2008/10/27 GARY MARKWALTER <gary.markwalter@hp.com> >> My question is this: >> I am running 7.31.FD2X9 >> What is the size in bytes of the following 2 data types? >> >> integer >> datetime year to fraction(3) >> >> Follow-up: where can i find this information in the manuals? DATETIME YEAR TO FRACTION(3) corresponds to DECIMAL(17,3); count the digits works remarkably well. YYYY-MM-DD HH:MM:SS.FFF And what is the size of DECIMAL(17,3)? Ah, glad you asked... That is 10 bytes, as is transparently obvious...oh, not to you; fair enough, it only took me 10 years or so... For a fixed point decimal like that, a decimal is stored with 1 byte for exponent and sign, plus 1 byte for each pair of digits before the decimal point, and 1 byte for each pair of digits after the decimal point (with odd digits occupying one byte). Hence, 17,3 means 14 digits before and 3 after the decimal point, for 7 bytes before and 2 after, or 9 bytes for digits plus 1 for exponent etc, leading to 10 in total. This information is derivable from the manual - it isn't always as easy as all that. Look in the Informix Guide to SQL: Reference manual in the chapter on data types. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.
On Mon, Oct 27, 2008 at 5:49 AM, Jack Parker <jack.parker4@verizon.net> wrote: > SQL Ref manual chapter. Integer is 4. Datetime is probably 8. INTEGER is 4; DATETIME is not necessarily (or even usually) 8. See my other answer - which deals with the situation more thoroughly. > From: ids-bounces@iiug.org On Behalf Of GARY MARKWALTER > My question is this: > I am running 7.31.FD2X9 > What is the size in bytes of the following 2 data types? > > integer > datetime year to fraction(3) > > Follow-up: where can i find this information in the manuals? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.
I should perhaps be a little less sweeping... On Wed, Oct 29, 2008 at 12:38 PM, Jonathan Leffler <jleffler.iiug@gmail.com> wrote: > On Mon, Oct 27, 2008 at 5:47 AM, Jean Sagi <jeansagi.ifx@gmail.com> wrote: >> At least for integer I think it depends on the machine you are running. > > No, it does not. SQL INTEGER is always 4 bytes. In Informix databases, SQL INTEGER is always, and has always been, 4 bytes. Other DBMS might have different rules. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.
cool to know ! J. 2008/10/29 Jonathan Leffler <jleffler.iiug@gmail.com> > On Mon, Oct 27, 2008 at 5:47 AM, Jean Sagi <jeansagi.ifx@gmail.com> wrote: > > At least for integer I think it depends on the machine you are running. > > No, it does not. SQL INTEGER is always 4 bytes. > > > 2008/10/27 GARY MARKWALTER <gary.markwalter@hp.com> > >> My question is this: > >> I am running 7.31.FD2X9 > >> What is the size in bytes of the following 2 data types? > >> > >> integer > >> datetime year to fraction(3) > >> > >> Follow-up: where can i find this information in the manuals? > > DATETIME YEAR TO FRACTION(3) corresponds to DECIMAL(17,3); count the > digits works remarkably well. > > YYYY-MM-DD HH:MM:SS.FFF > > And what is the size of DECIMAL(17,3)? Ah, glad you asked... > > That is 10 bytes, as is transparently obvious...oh, not to you; fair > enough, it only took me 10 years or so... > > For a fixed point decimal like that, a decimal is stored with 1 byte > for exponent and sign, plus 1 byte for each pair of digits before the > decimal point, and 1 byte for each pair of digits after the decimal > point (with odd digits occupying one byte). Hence, 17,3 means 14 > digits before and 3 after the decimal point, for 7 bytes before and 2 > after, or 9 bytes for digits plus 1 for exponent etc, leading to 10 in > total. > > This information is derivable from the manual - it isn't always as > easy as all that. > Look in the Informix Guide to SQL: Reference manual in the chapter on > data types. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ > "Blessed are we who can laugh at ourselves, for we shall never cease > to be amused." > NB: Please do not use this email for correspondence. > I don't necessarily read it every week, even. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
knowing the truth is beyond anything ... ;) J. 2008/10/29 Jonathan Leffler <jleffler.iiug@gmail.com> > I should perhaps be a little less sweeping... > > On Wed, Oct 29, 2008 at 12:38 PM, Jonathan Leffler > <jleffler.iiug@gmail.com> wrote: > > On Mon, Oct 27, 2008 at 5:47 AM, Jean Sagi <jeansagi.ifx@gmail.com> > wrote: > >> At least for integer I think it depends on the machine you are running. > > > > No, it does not. SQL INTEGER is always 4 bytes. > > In Informix databases, SQL INTEGER is always, and has always been, 4 bytes. > > Other DBMS might have different rules. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ > "Blessed are we who can laugh at ourselves, for we shall never cease > to be amused." > NB: Please do not use this email for correspondence. > I don't necessarily read it every week, even. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >