View previous topic :: View next topic |
Author |
Message |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Fri Jan 30, 2015 6:41 pm Post subject: Date + time source displayed as elapsed time |
|
|
Hi,
Is it possible to import data based on date/time (y-m-d-h-m-s) X-axis data and let the X-axis show only the elapsed time?
With random date/time entries the X-axis could nevertheless result in e.g. 0....36 hours.
Dataloggers usually log date and time but often elapsed (relative) time is more of interest than the absolute date and time.
I find date/time graphs much harder to read than elapsed time and the latter also looks much cleaner/better.
Alternatively the date can be hidden on the x-axis by mouse over X-axis and selecting the RMB option "Time of day (Fractional days)" but somehow this doesn't work well for the "Elapsed time" option (it shows the number 420 before every time tick mark and the time scale is not correct anymore) _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
DPlotAdmin Site Admin
![](images/avatars/12798672406543ad048b04c.png)
Joined: 24 Jun 2003 Posts: 2310 Location: Vicksburg, Mississippi
|
Posted: Fri Jan 30, 2015 10:59 pm Post subject: |
|
|
Edit>Operate on X with X=X-$X(1,1) will subtract the first date/time from all X. More generally you can use X=X-DATE(year,month,day).
The 420 prefix for the Elapsed Time number format is the number of days (which I assume you'll want to subtract from X). _________________ Visualize Your Data
support@dplot.com |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Sat Jan 31, 2015 7:24 am Post subject: |
|
|
Thanks but both suggestions only change the starting date to new years eve 1899: in the graph still the full date + time is shown. I only need elapsed time without date to be displayed.
If I set the x-axis to Date+Time the dates and times are correct (total time span of samples taken is two full DAYS).
When I set the X-axis view to Elapsed time or Time of Day (hours) then somehow the total time span changes to two ful HOURS.
The option "Time of Day (fractional days)" shows a correct scale, though.
But the latter shows only true hours (0-24 hours twice), not a cumulative time (0-48 hours).
PS: related to my previous posted topic: I had to change Decimal symbol to dot instead of comma to make your suggestions to work/not produce errors). Initially after making the change and entering a formula DPlot crashed but after restarting DPlot and entering the formula your examples were accepted by DPlot. _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
DPlotAdmin Site Admin
![](images/avatars/12798672406543ad048b04c.png)
Joined: 24 Jun 2003 Posts: 2310 Location: Vicksburg, Mississippi
|
Posted: Sat Jan 31, 2015 11:34 am Post subject: |
|
|
Quote: | Thanks but both suggestions only change the starting date to new years eve 1899: in the graph still the full date + time is shown. I only need elapsed time without date to be displayed. |
After using Operate on X, change the number format to "Elapsed Time" or "Time of Day (fractional days)".
Quote: | When I set the X-axis view to Elapsed time or Time of Day (hours) then somehow the total time span changes to two ful HOURS.
|
"Time of Day (hours)" assumes that the input is in hours. 1=1AM, 2=2AM, etc. "Time of Day (fractional days)" (which is what you have unless you're doing something else to manipulate the input) assumes fractions of days: 1=24 hrs, 2=48 hrs, etc. _________________ Visualize Your Data
support@dplot.com |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Sun Feb 01, 2015 9:43 am Post subject: |
|
|
After Operate on X selecting Elapsed time I still get a weird looking scaling Obviously it all is based on whole days and fraction of days (one day has an unpractical 60 divisions).
Example graph if you like to test: http://www.robemus.com/temp/Diepvrieskist_W-kWh-edit.grf
Goal is an X-axis like 0...48h with intermediate minute ticks. So not ticks like 1 day, 15/60th day,etc.
If not possible consider "elapsed time in hours with 1/60 minor ticks for minutes" as a feature request
I give up; looks like preparing CSV's before importing into DPlot or ignoring the date/time column while importing into DPlot (and later operate on X) is the only solution right now.
The latter only works if the source data has fixed time intervals between the samples, though. _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
DPlotAdmin Site Admin
![](images/avatars/12798672406543ad048b04c.png)
Joined: 24 Jun 2003 Posts: 2310 Location: Vicksburg, Mississippi
|
Posted: Sun Feb 01, 2015 1:44 pm Post subject: |
|
|
Sorry for all the trouble - I goofed in my description of the "Elapsed Time" number format. It expects input as hours (1=1 hr, 2=2 hrs, etc.), not fractional days as I said. So if you use Edit>Operate on X with X=(X-$X(1,1))*24 and then switch the number format to Elapsed Time you'll get what you want after playing around with the tick interval using Options>Extents/Intervals/Size. The following is your plot with the interval set to 1 hour and no intermediate tick marks.
http://www.dplot.com/forumimages/Diepvrieskist_W-kWh-edit_fixed.grf _________________ Visualize Your Data
support@dplot.com |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Sun Feb 01, 2015 5:36 pm Post subject: |
|
|
Yeaii!
Thanks. Never considered doing math on a date set but your solution works like a charm
One final question:
I "understand" from your "X=(X-$X(1,1))*24" expression that first the starting date is substracted from all entries and then all entries are multiplied by 24 (full scale of 2 becomes 48 ).
So basically now there are 24 as many "days" than before; (d-h-m-s x 24) in the date data set.
But what are the (1,1) variables exactly for? _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
DPlotAdmin Site Admin
![](images/avatars/12798672406543ad048b04c.png)
Joined: 24 Jun 2003 Posts: 2310 Location: Vicksburg, Mississippi
|
Posted: Mon Feb 02, 2015 7:42 am Post subject: |
|
|
The curve index and the point within that curve. So... first point in the first curve. _________________ Visualize Your Data
support@dplot.com |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Fri Feb 06, 2015 5:02 pm Post subject: |
|
|
Thanks!
Interesting math which I'm sure is useful to remember.
Sorry for the late reply; somehow it seems I never got email notifications of replies while having enabled that option _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
DPlotAdmin Site Admin
![](images/avatars/12798672406543ad048b04c.png)
Joined: 24 Jun 2003 Posts: 2310 Location: Vicksburg, Mississippi
|
Posted: Fri Feb 06, 2015 11:09 pm Post subject: |
|
|
The e-mail notice thing is my fault. This board uses PHP and I modified it heavily to get rid of spam posts, then the server switched PHP versions which broke many of the older board's features. I'll get it fixed eventually. _________________ Visualize Your Data
support@dplot.com |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Mon Jun 27, 2016 1:47 pm Post subject: |
|
|
Hi David,
Sorry to bother you again with an old issue showing up again but this time on Windows 10 32 bit:
Source data: X=date, Y=value
Operate on X: X=(X-$X(1,1))*24
Result on Windows 7 64 bit = OK
Result on Windows 10 32 bit = error: 'Syntax error: missing operator before open parenthesis'
Any idea what is causing this?
Cheers,
Roberto _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
DPlotAdmin Site Admin
![](images/avatars/12798672406543ad048b04c.png)
Joined: 24 Jun 2003 Posts: 2310 Location: Vicksburg, Mississippi
|
Posted: Mon Jun 27, 2016 10:22 pm Post subject: |
|
|
No. There's nothing about Windows 10 which would break this. Please send me a data set you're having trouble with at support@dplot.com. _________________ Visualize Your Data
support@dplot.com |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
DPlotAdmin Site Admin
![](images/avatars/12798672406543ad048b04c.png)
Joined: 24 Jun 2003 Posts: 2310 Location: Vicksburg, Mississippi
|
Posted: Tue Jun 28, 2016 7:56 am Post subject: |
|
|
Thanks for the file. I won't have a chance to try this on Windows 10 until tonight. But if you typed the equation as your e-mail title appears:
(X-($X(1,1)*24
then there's the problem. That should be
(X-$X(1,1))*24 _________________ Visualize Your Data
support@dplot.com |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Tue Jun 28, 2016 3:05 pm Post subject: |
|
|
Sorry for the email title typo.
I did use (X-$X(1,1))*24 indeed.
Even (X=) X-$X(1,1) down to just $X(1,1) causes an error (in single data set files).
Today I got me a new data set (supposed to be added to the previous graph) but exactly the same issue is showing up.
Roberto _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Tue Jun 28, 2016 3:09 pm Post subject: |
|
|
Forgot but perhaps of any interest:
Using Dutch number format so comma used for fraction instead of a dot. _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
DPlotAdmin Site Admin
![](images/avatars/12798672406543ad048b04c.png)
Joined: 24 Jun 2003 Posts: 2310 Location: Vicksburg, Mississippi
|
Posted: Tue Jun 28, 2016 4:14 pm Post subject: |
|
|
Quote: | Using Dutch number format so comma used for fraction instead of a dot. |
Eureka! Use $X(1;1) instead of $X(1,1). As is DPlot is interpreting $X(1,1) as $X(1.1), which makes no sense to it.
And tell your dang countrymen and the darn Germans to cut it out with comma decimal points! ![Very Happy](images/smiles/icon_biggrin.gif) _________________ Visualize Your Data
support@dplot.com |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Wed Jun 29, 2016 12:13 pm Post subject: |
|
|
YES, YES, YES; you did it again!
Honestly I suspected no problems on your end and getting left with work arounds
Uhhm, do I sense a tiny bit of accumulated comma and dot frustration, polite and subtlety expressed?
Oh yeah, I totally agree they finally should make a single world-wide standard for number notation.
Likely if just OS developers start with it then the format quickly will be used commonly among the ordinary people.
Now, which format is it going to be?
As always, thanks for looking into it, David!
Roberto _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Sun Nov 04, 2018 10:29 am Post subject: |
|
|
Hi David,
It seems I ran into a bug when Dplot converts date format x-values: the following screendump will be self-explanatory: [img]www.romus.nl/dump/DPlot-Date_conversion_bug.jpg[/img]
However, I am not sure if it is a serious bug. The thing is that I wanted to convert a data set to elapsed time based on hours (with your Operate on X: X-$X(1;1))*24 trick) and since it was not working I ran into the issue above. However, later I also found I need to respect orders of actions before applying the operate on X; otherwise the elapsed time to hours conversion doesn't work anyway.
[edit: text removed since I'm still struggling with correct order of actions when combining curves]
Anyway, like I said I do not know if the accidentally found bug is a significant one in praktice (people need to Operate on X while in Date and Time format?[edit: yes, apparently I did this evening on multiple curves]) but at least it looks like incorrect date conversion.
Roberto _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Mon Nov 05, 2018 2:01 pm Post subject: |
|
|
Hi David,
I'm really confused. The bug in my previous post is messing things up but apart from that I really can't see the logic of x-axis date and time values when setting different Number formats.
The test file I used is uploaded to [url]https:www.romus.nl/temp/Number-format-test.grf[/url]
• Opening the file everything is ok.
• When applying X = X-$X(1;1)) the date is offset to DPlot's very first date (date and time number 0 or 1 instead of thousands); plot looks fine but x-data are corrupted (sudden jump in date from ancient to 2018 at the first century switch)
• Also, when not modifying the X values at all, even then when changing number format to any date and time related number option, the time scale gets totally messed up: e.g. a period of originally hours changes to days or minutes.
Roberto _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Tue Nov 06, 2018 2:46 pm Post subject: |
|
|
Hi David,
Does, or rather: is it supposed that changing Number Format involves actual convertion of data or is it simply telling DPlot how to display the labels on the x-axis?
With the time scale being messed up when changing number format I start to get the impression DPlot is not converting anything but just displaying a preset format which requires the correct related number format of the actual x-values in the first place. The wrong time scaling after changing Number Format might be caused by DPlot interpreting the original data (with wrong format for intended Number format) not correctly.
E.g. "Date and Time" has either a date and time input (syntax dd-mm-yyyy hh:mm:ss) or a counter number input, starting with no. 1 at 1-1-1900, and output format dd-mm-yyyy hh:mm:ss...
while "Elapsed time" suspects a fractional number according to the help pages with syntax integer for hours and fraction for part of hours (minutes, seconds) like 1.5 for 1h, 30min.
If DPlot is not supposed to actually convert one to the other then the only way to get "Elapsed time" working properly is preparing the data file before opening it in DPlot?
Roberto _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
DPlotAdmin Site Admin
![](images/avatars/12798672406543ad048b04c.png)
Joined: 24 Jun 2003 Posts: 2310 Location: Vicksburg, Mississippi
|
Posted: Wed Nov 14, 2018 7:31 pm Post subject: |
|
|
I'm trying to catch up a bit here, but in the meantime here is how DPlot expects numbers internally for the various number formats involving dates and/or times:
Time of day (hours). Internally the numbers are in hours. 1=1AM, 1.5=1:30AM, etc.
Time of day (fractional days). Numbers are in days. 0.04166667 (1/24) = 1AM, for example.
Calendar date. Whole numbers are days. Uses the Excel date system. 1=1/1/1990.
Date and Time. Same as "Calendar date", but shows times as well. 1.5=1/1/1990 12:00:00 PM.
Elapsed time. Whole numbers are hours. -1.5->-1:30:00, 1.5->1:30:00
At first glance I think what you want is (X-$X(1;1))*24, but if the current number format is "Calendar date" or "Date and Time", this will result in nonsense values being shown until you change the number format to "Elapsed time", because you're now working with whole number hours rather than whole number days. _________________ Visualize Your Data
support@dplot.com |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Thu Nov 15, 2018 1:20 pm Post subject: |
|
|
So DPlot does not (, is supposed to) convert anything when choosing/ switching to a different format?
The time formats are just there for the sake of display options while the actual source data is supposed to have the right format in the first place?
I always was confused about this as setting a different date/time format results in change of x-values in the list of source data. The x-values changing made me assume DPlot actually converts one to the other.
Not knowing what DPlot is doing or at least assuming behind the screens is making things more difficult. E.g. what DPlot is using from a d-m-y h:m:s input when setting format to elapsed time with native format hours-fraction of hours: is it only processing the h:m:s part, is it assuming the d-m-y part as h:m:s, is it working with actual date/time numbers (counting up since 1-1-1900) and using only the first/last x digits, etc.
The help indeed clarified intended default input numbers/ranges so that should work any time. Yet the earlier success of date/time input data shown as elapsed time using your formula kind of reveils DPlot is actually converting numbers and not blindly assuming input data are in a specified fixed format.
Yes, what you came up with before (X-$X(1;1))*24 seemed to work like a charm but it looks like I didn't have data sets spanning more than 24 hours before.
...looking old DPlot files up... Oh my, I do have a 50 hours plot in elapsed time format! I will have a look at the source data formats to see why there are differences.
The 50 hour plot was made from y-m-d h:m:s (voltage data logger) data while the current problem data set has d-m-y h:m:s (new energy meter) data. Usually I pre-process data in a text editor and/or spreadsheet to a DPlot-friendly csv file but I never changed the date formats. I did set DPlot to the date format fitting the current data set, though. What I did NOT try is doing this BEFORE importing the data set. So now I have some hints to experiment with a bit more.
Amazing, so far ALL devices and/or software I have, capable of outputting data files, store the data differently in the txt/csv files and yet they claim to use the 'generally known csv format': use of comma, dot, (semi-)colon, minus, slash, (date &) time format is all different among data files.
The Elapsed time format in hours,fraction of hours is something I consider as a last resort: including that spreadsheet task in the data file preparation before opening in DPlot is a bit of a tedious or at least time consuming addition. But at least the option is there! _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Roberto
![](images/avatars/148800713254c443de373a9.png)
Joined: 16 Mar 2008 Posts: 102 Location: Netherlands Beek en Donk
|
Posted: Mon Nov 26, 2018 2:39 pm Post subject: |
|
|
Took a couple of weeks of experimenting but: issue SOLVED!
Turned out I overlooked an error in the Operate on X, X= expression:
What should always have been (X-$X(1;1))*24 somehow once was entered as (X-$X(1;1))/24 ... divide by 24 instead of multiply with 24.
This happens when reusing expressions from a history list and particularly with the font being so inconvenient darn tiny in the input box. The latter is prone to overlooking details easily.
Additionally it is not helping not understanding the expression anyway: (sample date/time minus first sample date/time) times 24 results in elapsed time with hours continue counting instead of repeat counting every day (24 hours)... perhaps this way the hours repeat counting every 24 days?? Didn't test that; ok as long as I don't have logging sessions taking more than 24 days
Also, with decimal symbol set to 'point' the expression must be (X-$X(1,1))*24 while with decimal symbol set to 'comma' the expression must be (X-$X(1;1))*24
Just using semi-colon as allround safe one (so not using comma at all) isn't working: Operate on X produces an error when using a semi-colon in the expression when decimal symbol is set to 'point'. _________________ Roberto |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
|