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
