Hi Ric, The reason why I use YYYYMMDD.frationalTime is primarily for convenience.
I actually considered using day number but I had to discard it since it is easier to debug an application where the driving data can be easily deduced through a pattern. Consider the following data: +---------------+------------+---+-------------+-------------+ |Production Line|Order Number|Qty|Start |End | +---------------+------------+---+-------------+-------------+ |1 |1 |100|20090909.3333|20090909.5 | +---------------+------------+---+-------------+-------------+ |1 |2 |100|20090909.54 |20090909.7083| +---------------+------------+---+-------------+-------------+ You can deduce without any conversion that for production line 1, order number 1 would run from 9/9/2009 8am to 12pm and order number 2 would run from 1pm to 5pm. If I would use the day number, I would have to convert it first to a proper date before I can validate that the business logic is running as expected. Also, since we have a local technical support in the factory (located across the world from me), I have allowed a "Debug Mode=ON" configuration settings in our application (as discussed in: http://www.jsoftware.com/jwiki/Guides/J%20VB.NET#Initialization ) which actually displays the J Session window and our technical support staff there can help in providing assistance in resolving a user issue. So I agree with you that it would have been more natural and efficient to store dates as day numbers but its more convenient overall to use the YYYYMMDD.fractionalTime format. > The code that I posted on the wiki optionally uses the format > <YYYYMMDD.hhmmsss>, one of the downsides of this format is that the > milliseconds are not stored accurately in > floating point, is that the > reason that you use "fractional time" instead? Regarding this, even using YYYYMMDD.fractionalTime, some results sometimes convert the data back with a few seconds or milliseconds off the original or expected values because of rounding errors. Particularly if you truncate your decimal values into a specific number of positions. Still, the my reasoning behind the use of fractional time over hhmmss is that it is easier/convenient during debugging and support. I just keep in mind that 0.5 is 12pm and work it out from there, your mileage may vary. Ohhh... I just remembered. One of the biggest motivation behind using fractional time was that when drawing/updating the Gantt chart bar, the C# front end does not have to do any data conversion when drawing the start and end time for a production line task. So if the tasks starts at 8am, the Gantt chart bar is drawn 33% into the day and if ending at 12pm only the first 50% of the day is drawn over. Keeping the code clean between the C# drawing front-end and the processing J backend. Which means (realization!), I've been using this method even with other projects because me and everyone else involved now thinks in fractional time. How about that!?! r/Alex -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Sherlock, Ric Sent: Thursday, September 10, 2009 3:17 PM To: 'Programming forum' Subject: Re: [Jprogramming] Extending dates.ijs Alex, Thanks for your description of how you use dates & times, I agree that handling time-spans is an important aspect to cover. You are using dates & times more often and for more "mission-critical" things than I am, so I'd appreciate your perspective on the following: You seem to use <YYYYMMDD.fractional time> as your base date/time format in J, which you convert to and from day number before doing your arithmetic. Why don't you just use day number as your base format except when outputting for human use? In fact if C#, VB.NET, MS-SQL offers the option of exporting the DateTime object as a value that you could import numerically into J, then it is probably some sort of Day number that can be converted to a J day number by the simple addition/subtraction of a constant that would clean up your import/export code. For example the constant for Excel is just 36522: DATEVALUE("10-Sep-2009 4:50 PM") gives the result: 40066 TIMEVALUE("10-Sep-2009 4:50 PM") gives the result: 0.701388889 getdate '10-Sep-2009' 2009 9 10 todayno getdate '10-Sep-2009' 76588 40066 + 36522 76588 J's getdate doesn't handle times yet but let's say we create getDateTime as an extension that returns: getDateTime '10-Sep-2009 4:50 PM' 2009 9 10 16 50 0.0 Then using the code I posted on the wiki: "http://www.jsoftware.com/jwiki/RicSherlock/Extend%20Dates%20Project/DatesAdd%20Script"; toDayNumber 2009 9 10 16 50 0.0 76588.7013889 NB. Subtract 36522 to convert to Excel result NB. Add 1.65 days to that: 1.65 + toDayNumber 2009 9 10 16 50 0.0 76590.3513889 NB. Convert back to J date time array toDateTime 1.65 + toDayNumber 2009 9 10 16 50 0.0 2009 9 12 8 26 0 NB. or convert to <YYYYMMDD.hhmmsss> format 1 toDateTime 1.65 + toDayNumber 2009 9 10 16 50 0.0 20090912.0826 The code that I posted on the wiki optionally uses the format <YYYYMMDD.hhmmsss>, one of the downsides of this format is that the milliseconds are not stored accurately in floating point, is that the reason that you use "fractional time" instead? Ric > From: Alex Rufon > > Hi Ric, > > After reading your email this morning, I went and check how I actually > used dates in J. Please keep in mind that I primarily read and save > data to and from MS-SQL Servers. The front end system is using C# or > VB.NET (depending on the project tech lead) so the data is read into > .NET to DataTable. > > To import a DataTime object from a DataTable to J, I actually convert > this first into a string in this format: MM/DD/YYYY HH:MM:SS.MS > <lang C#> > string objTemp = value.Month.ToString() + "/" + > value.Day.ToString() + "/" + value.Year.ToString(); > objTemp += " " + value.Hour.ToString() + ":" + > value.Minute.ToString() + ":" + value.Second.ToString(); > objTemp += "." + value.Millisecond.ToString(); > </lang> > > I then send this new string to J using J's setB() method. Then I > convert this to a proper number as YYYYMMDD.timeofday like so: > [dtemp=. 6!:0 '' > 2009 9 10 11 21 20.526 > [ddate=. 10000 100 100 #. 3 {. dtemp > 20090910 > [dtime=. 0.00001 * 100000 * (24 60 60 #. 3 }.dtemp) % 86400 > 0.473154236111111 > [ndate=. ddate + dtime > 20090910.4731542 > > Of course, after a few operations, I have to save this back to MS-SQL > by creating an SQL DML command (INSERT or UPDATE) but have to convert > this to the original date and time format first. > First off, it's simple enough to get this data back to the J date > format: > [vdate=. 10000 100 100 #: <. ndate > 2009 9 10 > [vtime=. 24 60 60 #: 0.00001 * (<....@+&0.5) 100000 * ndate * 86400 > 11 21 20.52587890625 > vdate, vtime > 2009 9 10 11 21 20.52587890625 > > The resulting data can now be easily converted back to MM/DD/YYYY > HH:MM:SS.MS which is required by SQL. > > To support this concept of date and time, I actually made the following > verbs: > NB. ========================================================= > NB.*dateplus (a) Adds day and fractional time to a date > NB. > NB. x is calendar date > NB. y is day and fractional time > NB. returns calendar date in YYYYMMDD.fractional time > > NB. ========================================================= > NB.*dateminus (a) Subtracts a day and fractional time to a date > NB. > NB. x is calendar date > NB. y is day and fractional time > NB. returns calendar date in YYYYMMDD.fractional time > > NB. ========================================================= > NB.*dateminusdate (a) Subtracts two dates and returns days and > fractional time > NB. > NB. x is calendar date > NB. y is calendar date > NB. returns days and fractional time > > I apologize for not posting the actual code since I heavily use the > "Primitives" and my own library ... hmmm ... to give you an idea, > dateplus actually looks like this: > NB. ========================================================= > NB.*dateplus (a) Adds day and fractional time to a date > NB. > NB. x is calendar date > NB. y is day and fractional time > NB. returns calendar date in YYYYMMDD.fractional time > dateplus=: dyad define > NB. Get the date and time > date=. rounddown x > time=. decimal x > ndate=. rounddown y > ntime=. decimal y > > NB. Start by adding the time, this to make sure we handle > NB. extra days > time=. time plus ntime > ndate=. ndate plus rounddown time > time=. decimal time > > NB. Now we calculate the actual date and return it in YYYYMMDD.time > (1 todate (1 todayno date) + ndate) + time > ) > > I heavily use this concept of Date and Time and the support verbs for > my Production Planning System which looks similar to MS-Project with > its drag and drop Gantt chart bars. I use GDI+ in .NET for drawing the > Gantt chart and all processing is done in J using a customized library > based on the .NET interface that can be found here: > http://www.jsoftware.com/jwiki/Guides/J%20VB.NET > > Wow, my email is long again. To summarize, in my use of J and datetime > datatype ... I found out that I only need three manipulation verbs > which are dateplus, dateminus, dateminusdate. I also find the native > array as a datetime format quite inconvenient and has to atomize it > into a YYYYMMDD.timefraction format. > > I hope this helps in any way with what you want to do. :) > > r/Alex > > > -----Original Message----- > From: [email protected] [mailto:programming- > [email protected]] On Behalf Of Sherlock, Ric > Sent: Thursday, September 10, 2009 9:08 AM > To: Programming forum > Subject: [Jprogramming] Extending dates.ijs > > Hi all, > Raul's recent request for extending getdate from the dates.ijs script, > got me thinking about my "wishlist" for handling dates as well as past > forum discussions suggesting extensions. > > I wonder if we (the J community) put our collective heads together, > whether we can come up with something more generally useful than the > individual efforts that I'm sure we currently all have tucked away. > > Some of the ideas that I've collated so far are: > * extend existing verbs to handle times as well as dates > * more flexible formatting of date & time string representations > * time-zone conversion? > > I've set up a page on the wiki to act as a focus for the project: > http://www.jsoftware.com/jwiki/RicSherlock/Extend%20Dates%20Project > > Any and all ideas/comments/offers of help/suggestions are solicited!! > > Ric > ---------------------------------------------------------------------- > For information about J forums see http://www.jsoftware.com/forums.htm > ---------------------------------------------------------------------- > For information about J forums see http://www.jsoftware.com/forums.htm ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
