FW: SELECT - using :: to convert datetime
Not a problem report but a tip: the poster shared that "SELECT current::datetime hour to second FROM systables WHERE tabid=1" converts a datetime's precision. Replies explained that "::" is simply a cast (equivalent to CAST(), and user-defined casts can extend it), and that no cast is needed at all — "SELECT current year to minute" works directly. Paul Watson and John Miller added that selecting from sysmaster:sysdual instead of systables is slightly faster (roughly 10-33% of a very small time), though Art Kagel noted the absolute saving is negligible for one-off queries.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I just found out about a function I never knew about - thought I'd share it, maybe someone else here also didn't know: Select current::datetime hour to second from systables where tabid = 1 You can play around with this, and use day, hour, second ...... NOTE: This e-mail message is subject to the MTN Group disclaimer see http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
Not sure if you got the full extent of this.... If yes, pardon me, if not here are some thoughts: "::" means a cast. You can also cast using CAST() funtion. It allows us to convert between different datatypes assuming the engine knows how to cast from one datatype to the other. We ca also create our own cast, which extends the engine features. This is normally seen when new datatypes are created (for example in datablades) So, what you're doing is converting a datetime (Current) into another with different precision. But in this case you could also use "SELECT CURRENT HOUR TO SECOND" Regards. On Thu, Sep 1, 2011 at 9:28 AM, Dirk Cornel.... <moolma_dc@mtn.co.za> wrote: > I just found out about a function I never knew about - thought I'd share > it, > maybe someone else here also didn't know: > > Select current::datetime hour to second > from systables > where tabid = 1 > > You can play around with this, and use day, hour, second ...... > > NOTE: This e-mail message is subject to the MTN Group disclaimer see > http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --002354332eda54903804abddccc6
You actually don't even need the cast, you can just: select current year to minute from systables where tabid = 1; > select current year to minute from systables where tabid = 1;> (expression) 2011-09-01 14:55 1 row(s) retrieved. > select current::datetime year to minute from systables where tabid = 1;> (expression) 2011-09-01 14:55 1 row(s) retrieved. It works just as well either way! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 1, 2011 at 4:28 AM, Dirk Cornel.... <moolma_dc@mtn.co.za> wrote: > I just found out about a function I never knew about - thought I'd share > it, > maybe someone else here also didn't know: > > Select current::datetime hour to second > from systables > where tabid = 1 > > You can play around with this, and use day, hour, second ...... > > NOTE: This e-mail message is subject to the MTN Group disclaimer see > http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba613bf0e125d304abe5e09b
And if you use sysmaster:sysdual then it should be a lot quicker select current year to minute from sysmaster:sysdual Cheers Paul > You actually don't even need the cast, you can just: > > select current year to minute > from systables where tabid = 1; > >> select current year to minute > from systables where tabid = 1;> > > (expression) > > 2011-09-01 14:55 > > 1 row(s) retrieved. >> select current::datetime year to minute > from systables where tabid = 1;> > > (expression) > > 2011-09-01 14:55 > > 1 row(s) retrieved. > > It works just as well either way! > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or > by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Thu, Sep 1, 2011 at 4:28 AM, Dirk Cornel.... <moolma_dc@mtn.co.za> > wrote: > >> I just found out about a function I never knew about - thought I'd share >> it, >> maybe someone else here also didn't know: >> >> Select current::datetime hour to second >> from systables >> where tabid = 1 >> >> You can play around with this, and use day, hour, second ...... >> >> NOTE: This e-mail message is subject to the MTN Group disclaimer see >> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --90e6ba613bf0e125d304abe5e09b > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid.
In my testing, on 1/10000 sec faster on average. But still, faster. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 1, 2011 at 3:06 PM, Paul Watson <paul@oninit.com> wrote: > And if you use sysmaster:sysdual then it should be a lot quicker > > select current year to minute from sysmaster:sysdual > > Cheers > Paul > > > You actually don't even need the cast, you can just: > > > > select current year to minute > > from systables where tabid = 1; > > > >> select current year to minute > > from systables where tabid = 1;> > > > > (expression) > > > > 2011-09-01 14:55 > > > > 1 row(s) retrieved. > >> select current::datetime year to minute > > from systables where tabid = 1;> > > > > (expression) > > > > 2011-09-01 14:55 > > > > 1 row(s) retrieved. > > > > It works just as well either way! > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other > > organization with which I am associated either explicitly, implicitly, or > > by > > inference. Neither do those opinions reflect those of other individuals > > affiliated with any entity with which I am affiliated nor those of the > > entities themselves. > > > > On Thu, Sep 1, 2011 at 4:28 AM, Dirk Cornel.... <moolma_dc@mtn.co.za> > > wrote: > > > >> I just found out about a function I never knew about - thought I'd share > >> it, > >> maybe someone else here also didn't know: > >> > >> Select current::datetime hour to second > >> from systables > >> where tabid = 1 > >> > >> You can play around with this, and use day, hour, second ...... > >> > >> NOTE: This e-mail message is subject to the MTN Group disclaimer see > >> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx > >> > >> > >> > >> > > > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > > > --90e6ba613bf0e125d304abe5e09b > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > -- > Paul Watson > Tel: +1 913-674-0360 > Mob: +1 913-387-7529 > Web: www.oninit.com > > www.advancedatatools.com > > Failure is not as frightening as regret. > If you want to improve, be content to be thought foolish and stupid. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba21241d36fdcb04abe670d6
These are so simple of query that you would expect the entire query to run fast either way. So I think the real question is what percentage of the entire query is 1/10000? On my system the response times are: 0.000139 select current year to minute from systables where tabid =3D 99 0.000093 select current year to minute from sysmaster:sysdual This means a 33% reduction is response times, but yes it is only a .00004 savings. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 09/01/2011 12:43 PM Subject: Re: FW: SELECT - using :: to convert datetime [24829] Sent by: ids-bounces@iiug.org In my testing, on 1/10000 sec faster on average. But still, faster. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinion= s and do not reflect on my employer, Advanced DataTools, the IIUG, nor any ot= her organization with which I am associated either explicitly, implicitly, = or by inference. Neither do those opinions reflect those of other individuals= affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 1, 2011 at 3:06 PM, Paul Watson <paul@oninit.com> wrote: > And if you use sysmaster:sysdual then it should be a lot quicker > > select current year to minute from sysmaster:sysdual > > Cheers > Paul > > > You actually don't even need the cast, you can just: > > > > select current year to minute > > from systables where tabid =3D 1; > > > >> select current year to minute > > > > > > > (expression) > > > > 2011-09-01 14:55 > > > > 1 row(s) retrieved. > >> select current::datetime year to minute > > from systables where tabid =3D 1;> > > > > (expression) > > > > 2011-09-01 14:55 > > > > 1 row(s) retrieved. > > > > It works just as well either way! > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and > > do not reflect on my employer, Advanced DataTools, the IIUG, nor an= y > other > > organization with which I am associated either explicitly, implicit= ly, or > > by > > inference. Neither do those opinions reflect those of other individ= uals > > affiliated with any entity with which I am affiliated nor those of = the > > entities themselves. > > > > On Thu, Sep 1, 2011 at 4:28 AM, Dirk Cornel.... <moolma_dc@mtn.co.z= a> > > wrote: > > > >> I just found out about a function I never knew about - thought I'd= share > >> it, > >> maybe someone else here also didn't know: > >> > >> Select current::datetime hour to second > >> from systables > >> where tabid =3D 1 > >> > >> You can play around with this, and use day, hour, second ...... > >> > >> NOTE: This e-mail message is subject to the MTN Group disclaimer s= ee > >> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx > >> > >> > >> > >> > > > > ***********************************************************************= ******** > >> Forum Note: Use "Reply" to post a response in the discussion forum= . > >> > >> > > > > --90e6ba613bf0e125d304abe5e09b > > > > > > > > ***********************************************************************= ******** > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > -- > Paul Watson > Tel: +1 913-674-0360 > Mob: +1 913-387-7529 > Web: www.oninit.com > > www.advancedatatools.com > > Failure is not as frightening as regret. > If you want to improve, be content to be thought foolish and stupid. > > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba21241d36fdcb04abe670d6 ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
I'm getting .000104 versus .000094 on my machine. Yes that's about a 10% savings but typically these are queries that are run once per session, so the absolute number is as important as the percentage. If it was a query that would run a million times a day and so save me 100 secs of runtime, great! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 1, 2011 at 4:21 PM, John Miller iii <miller3@us.ibm.com> wrote: > These are so simple of query that you would expect the entire > query to run fast either way. So I think the real question is > what percentage of the entire query is 1/10000? > > On my system the response times are: > > 0.000139 select current year to minute from systables where tabid > =3D 99 > 0.000093 select current year to minute from sysmaster:sysdual > > This means a 33% reduction is response times, but yes it is > only a .00004 savings. > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > From: "Art Kagel" <art.kagel@gmail.com> > To: ids@iiug.org > Date: 09/01/2011 12:43 PM > Subject: Re: FW: SELECT - using :: to convert datetime [24829] > Sent by: ids-bounces@iiug.org > > In my testing, on 1/10000 sec faster on average. But still, faster. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinion= > s > and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any ot= > her > organization with which I am associated either explicitly, implicitly, = > or > by > inference. Neither do those opinions reflect those of other individuals= > > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Thu, Sep 1, 2011 at 3:06 PM, Paul Watson <paul@oninit.com> wrote: > > > And if you use sysmaster:sysdual then it should be a lot quicker > > > > select current year to minute from sysmaster:sysdual > > > > Cheers > > Paul > > > > > You actually don't even need the cast, you can just: > > > > > > select current year to minute > > > from systables where tabid =3D 1; > > > > > >> select current year to minute > > > > > > > > > > (expression) > > > > > > 2011-09-01 14:55 > > > > > > 1 row(s) retrieved. > > >> select current::datetime year to minute > > > from systables where tabid =3D 1;> > > > > > > (expression) > > > > > > 2011-09-01 14:55 > > > > > > 1 row(s) retrieved. > > > > > > It works just as well either way! > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.com) > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > opinions > > > and > > > do not reflect on my employer, Advanced DataTools, the IIUG, nor an= > y > > other > > > organization with which I am associated either explicitly, implicit= > ly, > or > > > by > > > inference. Neither do those opinions reflect those of other individ= > uals > > > > affiliated with any entity with which I am affiliated nor those of = > the > > > entities themselves. > > > > > > On Thu, Sep 1, 2011 at 4:28 AM, Dirk Cornel.... <moolma_dc@mtn.co.z= > a> > > > wrote: > > > > > >> I just found out about a function I never knew about - thought I'd= > > share > > >> it, > > >> maybe someone else here also didn't know: > > >> > > >> Select current::datetime hour to second > > >> from systables > > >> where tabid =3D 1 > > >> > > >> You can play around with this, and use day, hour, second ...... > > >> > > >> NOTE: This e-mail message is subject to the MTN Group disclaimer s= > ee > > >> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx > > >> > > >> > > >> > > >> > > > > > > > > ***********************************************************************= > ******** > > > >> Forum Note: Use "Reply" to post a response in the discussion forum= > .. > > >> > > >> > > > > > > --90e6ba613bf0e125d304abe5e09b > > > > > > > > > > > > > > ***********************************************************************= > ******** > > > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > > > > > -- > > Paul Watson > > Tel: +1 913-674-0360 > > Mob: +1 913-387-7529 > > Web: www.oninit.com > > > > www.advancedatatools.com > > > > Failure is not as frightening as regret. > > If you want to improve, be content to be thought foolish and stupid. > > > > > > > > > ***********************************************************************= > ******** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --90e6ba21241d36fdcb04abe670d6 > > ***********************************************************************= > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > = > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba2123936f5b7004abe759c5