Current time since midnight - how do I get it?
A newcomer wanted the current time expressed as minutes since midnight, but arithmetic on CURRENT HOUR TO HOUR / MINUTE TO MINUTE gave syntax errors. The group explained Informix can't cast datetime/interval straight to integer, so you must cast via a character string: e.g. ((current hour to hour)::char(2))::int * 60 + ((current minute to minute)::char(2))::int. Cleaner/faster alternatives offered were ((current - today)::interval minute(4) to minute)::char(5)::int (or subtracting extend('00:00',hour to minute)), selecting from sysmaster:sysdual rather than systables for speed, and wrapping it in a user function or defining implicit casts. Note char(4) can overflow later in the day, so use char(5).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Newbie here (well, Informix newbie at any rate): I'm struggling to get the time of day in minutes since midnight. The apparent absence of HOUR and MINUTE functions are posing a challenge. I've tried select current hour to hour from systables where tabid=1; which gets me the hour, and select current minute to minute from systables where tabid=1; which gets me the minutes part of the current hour, but select (60 * (current hour to hour) + current minute to minute) from systables where tabid=1; gives a syntax error at the end of the line. I've also tried select (60 * (select current hour to hour from systables where tabid=1) + select current minute to minute from systables where tabid=1 ) from systables where tabid=1; but I get a syntax error here too. It seems the problem is introducing the arithmetic. How does one get the current time in minutes from midnight, using a single query? TIA Neil.
select current hour to second from systables where tabid=3D1 "Current hour to second" is the current time minus the date component. j. On Jul 12, 2011, at 6:30 AM, NEIL HAUGHTON wrote: > Newbie here (well, Informix newbie at any rate):=20 >=20 > I'm struggling to get the time of day in minutes since midnight. The = apparent=20 > absence of HOUR and MINUTE functions are posing a challenge. I've = tried=20 >=20 > select current hour to hour from systables where tabid=3D1;=20 > which gets me the hour, and=20 >=20 > select current minute to minute from systables where tabid=3D1;=20 > which gets me the minutes part of the current hour, but=20 >=20 > select (60 * (current hour to hour) + current minute to minute) from = systables=20 > where tabid=3D1;=20 >=20 > gives a syntax error at the end of the line.=20 >=20 > I've also tried=20 >=20 > select (60 * (select current hour to hour from systables where = tabid=3D1) +=20 > select current minute to minute from systables where tabid=3D1 ) from = systables=20 > where tabid=3D1;=20 >=20 > but I get a syntax error here too. It seems the problem is introducing = the=20 > arithmetic.=20 >=20 > How does one get the current time in minutes from midnight, using a = single=20 > query?=20 >=20 > TIA=20 >=20 > Neil.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
Jackie, Thanks for the quick response. That doesn't quite give me what I want. I need a number (integer) which represents the number of minutes since midnight. Your suggestion gets me (say) "11:54" when I need "714" (ie 60 * 11 + 54). I can do this quite easily in Sql Server and Oracle, but Informix is proving a tougher nut to crack.
So what you want is something like: select (current hour to hour * 60) + current minute to minute from systables where tabid=3D1 But you can't go from a datetime to an integer. SO you have to cast = through a character first: select (((current hour to hour::char(2))::integer)*60) =20 + ((current minute to minute::char(2))::integer) from systables where tabid=3D1 j. (Early morning is about the only time I get a chance to answer = questions). On Jul 12, 2011, at 6:56 AM, NEIL HAUGHTON wrote: > Jackie,=20 >=20 > Thanks for the quick response.=20 >=20 > That doesn't quite give me what I want. I need a number (integer) = which=20 > represents the number of minutes since midnight. Your suggestion gets = me (say)=20 > "11:54" when I need "714" (ie 60 * 11 + 54).=20 >=20 > I can do this quite easily in Sql Server and Oracle, but Informix is = proving a=20 > tougher nut to crack.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
A quick, tricky and dirty solution for your task: select (60 * (current hour to hour)::char(4)::int) + (current minute to minute )::char(4)::int from systables where tabid=1; Regards, Gerd -----Ursprüngliche Nachricht----- Von: "NEIL HAUGHTON" <neil.haughton@autoscribe.co.uk> Gesendet: Jul 12, 2011 12:30:54 PM An: ids@iiug.org Betreff: Current time since midnight - how do I get it? [24295] >Newbie here (well, Informix newbie at any rate): > >I'm struggling to get the time of day in minutes since midnight. The apparent >absence of HOUR and MINUTE functions are posing a challenge. I've tried > >select current hour to hour from systables where tabid=1; >which gets me the hour, and > >select current minute to minute from systables where tabid=1; >which gets me the minutes part of the current hour, but > >select (60 * (current hour to hour) + current minute to minute) from systables >where tabid=1; > >gives a syntax error at the end of the line. > >I've also tried > >select (60 * (select current hour to hour from systables where tabid=1) + >select current minute to minute from systables where tabid=1 ) from systables >where tabid=1; > >but I get a syntax error here too. It seems the problem is introducing the >arithmetic. > >How does one get the current time in minutes from midnight, using a single >query? > >TIA > >Neil. > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > ___________________________________________________________ Schon gehört? WEB.DE hat einen genialen Phishing-Filter in die Toolbar eingebaut! http://produkte.web.de/go/toolbar
Thanks to all those who leapt to my assistance! I've plumped for the suggested solution along the lines of select ((current hour to hour::char(2))::integer) * 60 + ((current minute to minute::char(2))::integer) etc.... as it works nicely for me. I was almost there myself, but I didn't know about the two step casting to integer through char. You either know this, or you don't! If anyone has a more elegant solution I'll be happy to see it! Neil.
Select (current hour to minute - '00:00' datetime hour to minute)::interval minute(4) to minute from systables where tabid = 1; That will return an interval type.if you need Ian integer cast the result to char(4) then cast that result to integer (there is not direct cast from interval to int). Art On Jul 12, 2011 6:31 AM, "NEIL HAUGHTON" <neil.haughton@autoscribe.co.uk> wrote: > Newbie here (well, Informix newbie at any rate): > > I'm struggling to get the time of day in minutes since midnight. The apparent > absence of HOUR and MINUTE functions are posing a challenge. I've tried > > select current hour to hour from systables where tabid=1; > which gets me the hour, and > > select current minute to minute from systables where tabid=1; > which gets me the minutes part of the current hour, but > > select (60 * (current hour to hour) + current minute to minute) from systables > where tabid=1; > > gives a syntax error at the end of the line. > > I've also tried > > select (60 * (select current hour to hour from systables where tabid=1) + > select current minute to minute from systables where tabid=1 ) from systables > where tabid=1; > > but I get a syntax error here too. It seems the problem is introducing the > arithmetic. > > How does one get the current time in minutes from midnight, using a single > query? > > TIA > > Neil. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --20cf307f325a4da2cb04a7de0393
Do you mean Select (((current hour to minute - '00:00' datetime hour to minute)::interval minute(4) to minute)::char(4))::integer from systables where tabid = 1; ? Even better! Thanks, Neil.
I get syntax error and once fixed an alloted space error on the SQL as posted try Select ((current hour to minute - (extend('00:00', hour to minute)) )::interval minute(4) to minute)::char(5)::integer from systables where tabid = 1; runs on my 11.5 Cheers Paul > Do you mean > > Select (((current hour to minute - '00:00' datetime hour to > minute)::interval > minute(4) to minute)::char(4))::integer from systables where tabid = 1; > > ? > > Even better! > > Thanks, > > Neil. > > > ******************************************************************************* > 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.
Also Select ( (current hour to minute - (extend('00:00', hour to minute)))::interval minute(4) to minute)::char(5)::integer from systables where tabid = 1; is 10x faster than select ((current hour to hour::char(2))::integer) * 60 + ((current minute to minute::char(2) )::integer) from systables where tabid =1 on my IDS 11.5. Personally I'd create a function based on the SQL and just use the function Cheers Paul > I get syntax error and once fixed an alloted space error on the SQL as > posted > > try > > Select ((current hour to minute - (extend('00:00', hour to minute)) > > )::interval minute(4) to minute)::char(5)::integer > from systables > where tabid = 1; > > runs on my 11.5 > > Cheers > Paul > >> Do you mean >> >> Select (((current hour to minute - '00:00' datetime hour to >> minute)::interval >> minute(4) to minute)::char(4))::integer from systables where tabid = 1; >> >> ? >> >> Even better! >> >> Thanks, >> >> Neil. >> >> >> > ******************************************************************************* >> 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. > -- 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.
I posted one elegant solution ;-), but one thing you can do to simplify your
SQL into how you would like to see it is to define implicit casts from
'datetime hour to hour' to integer and from 'datetime minute to minute' to
integer:
create function cast_hours( in_dt datetime hour to hour ) returning integeras results;
define ret as integer;
let ret = (in_dt::char(4))::int;
return ret;
end function;
create implicit cast (datetime hour to hour as int with cast_hours);
create function cast_minutes( in_dt datetime minute to minute ) returninginteger as results;
define ret as integer;
let ret = (in_dt::char(4))::int;
return ret;
end function;
create implicit cast (datetime minute to minute as int with cast_minutes);
Once you do that, then this query will work:
select (current hour to hour) * 60 + (current minute to minute)
from systables where tabid = 1;
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 Tue, Jul 12, 2011 at 7:51 AM, NEIL HAUGHTON <
neil.haughton@autoscribe.co.uk> wrote:
> Thanks to all those who leapt to my assistance! I've plumped for the
> suggested
> solution along the lines of
>
> select ((current hour to hour::char(2))::integer) * 60 + ((current minute
> to
> minute::char(2))::integer) etc....
>
> as it works nicely for me. I was almost there myself, but I didn't know
> about
> the two step casting to integer through char. You either know this, or you
> don't!
>
> If anyone has a more elegant solution I'll be happy to see it!
>
> Neil.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf3071c6f080c7bc04a7de78ea
If you want to make this 25% faster then there is a simple trick. do not query systables, but rather use sysmaster:sysdual as this is an in memory tables and does not need to retrieve data from an index. select ( (current hour to minute - (extend('00:00', hour to minute)))::interval minute(4) to minute)::char(5)::integer from sysmaster:sysdual; John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 07/12/2011 05:27:12 AM: > From: "Paul Watson" <paul@oninit.com> > To: ids@iiug.org > Date: 07/12/2011 05:29 AM > Subject: Re: Current time since midnight - how do I get it? [24304] > Sent by: ids-bounces@iiug.org > > Also > > Select ( (current hour to minute - (extend('00:00', hour to > minute)))::interval minute(4) to > minute)::char(5)::integer from systables where tabid = 1; > > is 10x faster than > > select ((current hour to hour::char(2))::integer) * 60 + ((current minute > to minute::char(2) > )::integer) from systables where tabid =1 > > on my IDS 11.5. > > Personally I'd create a function based on the SQL and just use the function > > Cheers > Paul > > > I get syntax error and once fixed an alloted space error on the SQL as > > posted > > > > try > > > > Select ((current hour to minute - (extend('00:00', hour to minute)) > > > > )::interval minute(4) to minute)::char(5)::integer > > from systables > > where tabid = 1; > > > > runs on my 11.5 > > > > Cheers > > Paul > > > >> Do you mean > >> > >> Select (((current hour to minute - '00:00' datetime hour to > >> minute)::interval > >> minute(4) to minute)::char(4))::integer from systables where tabid = 1; > >> > >> ? > >> > >> Even better! > >> > >> Thanks, > >> > >> Neil. > >> > >> > >> > > > ******************************************************************************* > >> 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. > > > > -- > 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. >
And we all always forget that trick :) -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John Miller iii Sent: Tuesday, July 12, 2011 11:30 AM To: ids@iiug.org Subject: Re: Current time since midnight - how do I get it? [24318] If you want to make this 25% faster then there is a simple trick. do not query systables, but rather use sysmaster:sysdual as this is an in memory tables and does not need to retrieve data from an index. select ( (current hour to minute - (extend('00:00', hour to minute)))::interval minute(4) to minute)::char(5)::integer from sysmaster:sysdual; John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 07/12/2011 05:27:12 AM: > From: "Paul Watson" <paul@oninit.com> > To: ids@iiug.org > Date: 07/12/2011 05:29 AM > Subject: Re: Current time since midnight - how do I get it? [24304] > Sent by: ids-bounces@iiug.org > > Also > > Select ( (current hour to minute - (extend('00:00', hour to > minute)))::interval minute(4) to > minute)::char(5)::integer from systables where tabid = 1; > > is 10x faster than > > select ((current hour to hour::char(2))::integer) * 60 + ((current minute > to minute::char(2) > )::integer) from systables where tabid =1 > > on my IDS 11.5. > > Personally I'd create a function based on the SQL and just use the function > > Cheers > Paul > > > I get syntax error and once fixed an alloted space error on the SQL as > > posted > > > > try > > > > Select ((current hour to minute - (extend('00:00', hour to minute)) > > > > )::interval minute(4) to minute)::char(5)::integer > > from systables > > where tabid = 1; > > > > runs on my 11.5 > > > > Cheers > > Paul > > > >> Do you mean > >> > >> Select (((current hour to minute - '00:00' datetime hour to > >> minute)::interval > >> minute(4) to minute)::char(4))::integer from systables where tabid = 1; > >> > >> ? > >> > >> Even better! > >> > >> Thanks, > >> > >> Neil. > >> > >> > >> > > > **************************************************************************** *** > >> 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. > > > > -- > 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. > **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. _____ avast! Antivirus <http://www.avast.com>; : Outbound message clean. Virus Database (VPS): 110712-0, 07/12/2011 Tested on: 7/12/2011 11:33:49 AM avast! - copyright (c) 1988-2011 ALWIL Software.
NEIL HAUGHTON Wrote: -------------------------------------------------------------------------------- Do you mean Select (((current hour to minute - '00:00' datetime hour to minute)::interval minute(4) to minute)::char(4))::integer from systables where tabid = 1; ? Even better! Thanks, Neil. -------------------------------------------------------------------------------- Response: Here is another slightly different take... select ((current - today)::interval minute(4) to minute)::char(5)::int from sysmaster:sysdual; When comparing today with a datetime variable it takes on a timestamp of midnight, so (current - today) gives the current time of day as an interval. The cast to interval minute(4) to minute gets the desired granularity. The subsequent casts get it to an integer.
Would hope not as the cast to char(4) will fail later in the day and John's version is significantly quicker. The select from sysdual instead of systables is a major performance gain - one that is regularly overlooked Cheers Paul > NEIL HAUGHTON Wrote: > > -------------------------------------------------------------------------------- > > Do you mean > > Select (((current hour to minute - '00:00' datetime hour to > minute)::interval > > minute(4) to minute)::char(4))::integer from systables where tabid = 1; > > ? > > Even better! > > Thanks, > > Neil. > > -------------------------------------------------------------------------------- > > Response: > Here is another slightly different take... > > select ((current - today)::interval minute(4) to minute)::char(5)::int > from sysmaster:sysdual; > > When comparing today with a datetime variable it takes on a timestamp of > midnight, so (current - today) gives the current time of day as an > interval. > The cast to interval minute(4) to minute gets the desired granularity. The > subsequent casts get it to an integer. > > > ******************************************************************************* > 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.