RE: Convert DATETIME YEAR TO FRACTION to INTEGER/DECIMAL value

What I am looking for is to store the DATETIME YEAR TO FRACTION, actually the MONTH TO FRACTION is a shorter notation in a string. I have only got a limited amount of space available. Currently I do the following: Let say the datetime value is '2005-10-19 13:34:45.326' is store the MMDDhhmmssfff value in a string, giving me something like '1019133445326' (13 characters). Obviously I would like to include the century and year, but anything that I can do to shorten the string to about 8 characters (even if I have to stay without the century and year). Any other Ideas? David -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Jonathan Leffler Sent: Thursday, October 20, 2005 08:12 AM To: informix-list@iiug.org Subject: Re: Convert DATETIME YEAR TO FRACTION to INTEGER/DECIMAL value David Reed wrote: > If I would like to convert a DATETIME value like '2005-10-19 > 00:01:34.346' to a INTEGER or DECIMAL value like '12345' how would I go > about? You need to be a lot more precise specifying what you want. create function dt_to_integer(x datetime year to fraction) returns integer;define i integer; if x = datetime(2005-10-19 00:01:34.346') year to fraction then let i = 12345; else let i = null; end if; return i; end function; You probably didn't mean that - but what did you mean? > I suppose Informix also stores dates as INTEGER/DECIMAL value? Informix stores DATE as an INTEGER, yes; RTFM. Informix stores DATETIME as a DECIMAL, yes; RTFM. > Is there a FUNCTION available? Lots of them - but probably not what you're looking for. Internally, the DECIMAL value stored for your example value is: 20051019000123.346 On the client, you can obtain that from a DATETIME value quite easily: #include "datetime.h" dec_t get_raw_decimal_from_datetime(const dtime_t *dt) { return(dt->dt_dec); } Or, in more idiomatic style: int get_raw_decimal_from_datetime(const dtime_t *dt, dec_t *rv) { *rv = dt->dt_dec; return(0); } This would work in C or C++ - and hence with ODBC too. More succinctly, you can simply reference dt->dt_dec, too, without using a function call, even. If you want to do that in pure SPL or SQL, you have to work a lot harder, and so does the server. Or you code a function in C rather like the ones I showed and build it into a 'datablade' and use that. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ sending to informix-list

David Reed wrote: > What I am looking for is to store the DATETIME YEAR TO FRACTION, > actually the MONTH TO FRACTION is a shorter notation in a string. I have > only got a limited amount of space available. Currently I do the > following: > > Let say the datetime value is '2005-10-19 13:34:45.326' is store the > MMDDhhmmssfff value in a string, giving me something like > '1019133445326' (13 characters). Obviously I would like to include the > century and year, but anything that I can do to shorten the string to > about 8 characters (even if I have to stay without the century and > year). > > Any other Ideas? Informix uses 10 bytes on disk to store that information, though I'd accept an argument that it could be stored in 9 (there'd be a lot of redefinition to do to get that to happen). If we can drop the year, then we can encode each digit pair in a Base-64 style notation: 00 -> 0 09 -> 9 10 -> A 35 -> Z 36 -> a 60 -> w This even sorts in order, which is a plus point - it is also reversible. Hence, you encode your value as (mental arithmetic - unverified): AJLYjWw Whether you can encode the year (excluding century) depends on the range of year numbers you need to deal with. If it is the full range (100 values), a plain Base-64 encoding has problems. If you can get away with the range 00..63, then you can add a leading character encoding the year (so, you'd add a 5 before the A). This encoding also wastes a digit as it encode the 3 fraction as 10, 20, 30, ... AARGH!!! The fractions need 00..99 too. OK, you get to play with the idea and decide the final encoding for fractions and/or years. Are you sure you need to do this much compression? Who needs to decode the value? Are you really sure about the 8-byte limit? > -----Original Message----- > From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] > On Behalf Of Jonathan Leffler > Sent: Thursday, October 20, 2005 08:12 AM > To: informix-list@iiug.org > Subject: Re: Convert DATETIME YEAR TO FRACTION to INTEGER/DECIMAL value > > David Reed wrote: > >>If I would like to convert a DATETIME value like '2005-10-19 >>00:01:34.346' to a INTEGER or DECIMAL value like '12345' how would I > > go > >>about? > > > You need to be a lot more precise specifying what you want. > > create function dt_to_integer(x datetime year to fraction) > returns integer;> define i integer; > if x = datetime(2005-10-19 00:01:34.346') year to fraction then > let i = 12345; > else > let i = null; > end if; > return i; > end function; > > You probably didn't mean that - but what did you mean? > > >>I suppose Informix also stores dates as INTEGER/DECIMAL value? > > > Informix stores DATE as an INTEGER, yes; RTFM. > Informix stores DATETIME as a DECIMAL, yes; RTFM. > > >>Is there a FUNCTION available? > > > Lots of them - but probably not what you're looking for. > > Internally, the DECIMAL value stored for your example value is: > > 20051019000123.346 > > On the client, you can obtain that from a DATETIME value quite easily: > > #include "datetime.h" > > dec_t get_raw_decimal_from_datetime(const dtime_t *dt) > { > return(dt->dt_dec); > } > > Or, in more idiomatic style: > > int get_raw_decimal_from_datetime(const dtime_t *dt, dec_t *rv) > { > *rv = dt->dt_dec; > return(0); > } > > This would work in C or C++ - and hence with ODBC too. More succinctly, > > you can simply reference dt->dt_dec, too, without using a function call, > > even. > > If you want to do that in pure SPL or SQL, you have to work a lot > harder, and so does the server. Or you code a function in C rather like > > the ones I showed and build it into a 'datablade' and use that. > -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/