| View previous topic :: View next topic |
| Author |
Message |
jeremywilson
Joined: 11 Jul 2011
Posts: 79
Location: United States
|
 Posted: Thu Feb 20, 2020 12:21 am Post subject: system time conversion |
 |
|
I need help converting the Bascom system time (seconds from 12:00am, 1/1/2000) to Excel/Windows system time (days from 12:00am, 1/1/1900).
My program logs data and records the Bascom system time, but I'd rather record the Excel system time so users don't have to make the change.
I believe the conversion to be:
Excel time = Bascom time / 86400 + 36526
If I record Bascom time and convert in Excel using the above formula the results are correct.
However, if I do the conversion in the micro, the results are weird.
The time resolution is 377.5 seconds instead of 1 second.
I tried splitting up the division, but no luck.
| Code: |
Temp_long = syssec()
Temp_single = Temp_long
Temp_single = Temp_single / 86400
Temp_single = Temp_single + 36526
Msg = Fusing(temp_single , "#.######")
|
example correct numbers:
date/time: 2/19/2020 11:23:16 am
Bascom time: 635426596
Excel time: 43880.4744907407
Thanks !!!
[/code]
(BASCOM-AVR version : 2.0.8.0 , Latest : 2.0.8.2 ) |
|
| Back to top |
|
 |
laborratte
Joined: 27 Jul 2005
Posts: 305
Location: Berlin
|
| Posted: Thu Feb 20, 2020 1:16 am Post subject: |
|
|
| The "Excel" time has too many significant digits for datatype single. You can use double for your calculation, but unfortunately fusing doesn't work with double - you have to figure out a formatting routine by yourself. |
|
| Back to top |
|
|
jeremywilson
Joined: 11 Jul 2011
Posts: 79
Location: United States
|
| Posted: Thu Feb 20, 2020 1:19 am Post subject: |
|
|
| Thanks for the feedback!! |
|
| Back to top |
|
|
EDC
Joined: 26 Mar 2014
Posts: 1133
|
| Posted: Thu Feb 20, 2020 1:44 am Post subject: |
|
|
This is the quote from the Help topic "Syssec". Maybe you dont need to divide but only add or sustract some magic value to/from Bascom Time 
| Quote: | The Return-Value is in the Range of 0 to 2147483647. 2000-01-01 at 00:00:00 starts with 0.
The Function is valid from 2000-01-01 to 2068-01-19 03:14:07. In the year 2068 a LONG – overflow will occur.
Unix time stamp starts 1-1-1970 which will limit the use till 2038.
Bascom time stamp starts 1-1-2000 giving longer working time.
If you wish to convert to NTP which starts at 1.1.1970, which is 30 years earlier, you need to subtract a value of 946684800
BASCOM DATE_TIME = NTP - 946684800
|
|
|
| Back to top |
|
|
jeremywilson
Joined: 11 Jul 2011
Posts: 79
Location: United States
|
| Posted: Fri Feb 21, 2020 6:08 pm Post subject: |
|
|
The division is needed since Bascom time is in seconds and Excel time is in days (86,400 seconds per day).
I tried converting the long to double, do the math, then convert to single but too much resolution is still lost.
What's the best way to convert a double into a string (like fusing does for singles)?
Thanks. |
|
| Back to top |
|
|
albertsm
Joined: 09 Apr 2004
Posts: 6197
Location: Holland
|
| Posted: Fri Feb 21, 2020 9:52 pm Post subject: |
|
|
if you want to convert to something for Excel why dont you use the DATE() and TIME() functions?
seems the best and simplest way to me.
and do not confuse with date$ and time$.
_________________
Mark |
|
| Back to top |
|
|
MWS
Joined: 22 Aug 2009
Posts: 2335
|
| Posted: Fri Feb 21, 2020 10:41 pm Post subject: |
|
|
| jeremywilson wrote: | The division is needed since Bascom time is in seconds and Excel time is in days (86,400 seconds per day).
I tried converting the long to double, do the math, then convert to single but too much resolution is still lost.
What's the best way to convert a double into a string (like fusing does for singles)?
Thanks. |
It works well with a single by using its available range solely for the decimal places of the Excel time format and the long for its integer part. |
|
| Back to top |
|
|
jeremywilson
Joined: 11 Jul 2011
Posts: 79
Location: United States
|
| Posted: Fri Feb 21, 2020 11:07 pm Post subject: |
|
|
I already have the time and date (from DS1308) and is fed into the Bascom date/time functions to get system time. Since our customers (users) are all over the world, my program allows them to choose from many different date/time regional formats to show on the screen. However, I use the system time for data logging to save space. My code logs data into a flash chip that the user can later transfer to the memory token (SD card-compatible). If the date/time aren't formatted properly when exported, the user would have to do some work before being able to plot the data.
Many of the on-screen date formats aren't recognized by Excel as a date. I'm just trying to save code space by not reformatting the date/time during export.
I'll try using the single for the decimals and long for the integer part.
Thanks! |
|
| Back to top |
|
|
MWS
Joined: 22 Aug 2009
Posts: 2335
|
| Posted: Sat Feb 22, 2020 4:17 pm Post subject: |
|
|
As example...
| Code: | Dim Temp1_long As Long
Dim Temp2_long As Long
Dim Temp3_long As Long
Dim DaySecs_single As Single
Dim predec_str As String * 10
Dim dec_str As String * 10
Dim excel_str As String * 20
Time$ = "22:25:33"
Do
Temp1_long = syssec()
Temp2_long = Temp1_long / 86400
Temp3_long = Temp2_long + 36526
Temp2_long = Temp2_long * 86400
Temp1_long = Temp1_long - Temp2_long
DaySecs_single = Temp1_long / 86400
dec_str = Fusing(DaySecs_single, "#.######")
dec_str = Right(dec_str , 7)
predec_str = Str(Temp3_long)
excel_str = predec_str + dec_str
Print excel_str
Loop |
|
|
| Back to top |
|
|
jeremywilson
Joined: 11 Jul 2011
Posts: 79
Location: United States
|
| Posted: Sat Feb 22, 2020 5:14 pm Post subject: |
|
|
Thanks for the post, MWS!
Have you tried the code that you posted?
It looks like this line would equal zero:
| Code: | Temp1_long = Temp1_long - Temp2_long |
because:
| Code: |
Temp2_long = Temp1_long / 86400
...
Temp2_long = Temp2_long * 86400 |
therefore, Temp2_long = Temp1_long. |
|
| Back to top |
|
|
jeremywilson
Joined: 11 Jul 2011
Posts: 79
Location: United States
|
| Posted: Sat Feb 22, 2020 5:27 pm Post subject: |
|
|
I'll try this on Monday. Not sure if the MOD is needed, but it makes the numerator a whole lot smaller.
| Code: |
Lcl_long2 = Lcl_long / 86400
Lcl_long2 = Lcl_long2 + 36526 'integer part
Lcl_single = Lcl_long Mod 86400
Lcl_single = Lcl_single / 86400 'decimal part
Msg = Fusing(lcl_single , "#.######")
Msg = Right(msg , 7)
Msg = Str(lcl_long2) + Msg
|
(Lcl_long holds the system time) |
|
| Back to top |
|
|
jeremywilson
Joined: 11 Jul 2011
Posts: 79
Location: United States
|
| Posted: Sat Feb 22, 2020 6:22 pm Post subject: |
|
|
After playing with a single precision calculator, I realized that the MOD needs to be done as Long.
https://www.binaryconvert.com/convert_float.html
635569066 would change to 635569088 when stored as a 32-bit single (a loss of 22 seconds - not acceptable).
| Code: |
Lcl_long2 = Lcl_long / 86400
Lcl_long2 = Lcl_long2 + 36526 'integer part
Lcl_long3 = Lcl_long Mod 86400
Lcl_single = Lcl_long3
Lcl_single = Lcl_single / 86400 'decimal part
Msg = Fusing(lcl_single , "#.######")
Msg = Right(msg , 7)
Msg = Str(lcl_long2) + Msg
|
|
|
| Back to top |
|
|
MWS
Joined: 22 Aug 2009
Posts: 2335
|
| Posted: Sat Feb 22, 2020 8:13 pm Post subject: |
|
|
| jeremywilson wrote: | | I'll try this on Monday. Not sure if the MOD is needed, but it makes the numerator a whole lot smaller. |
If you've missed it, and to answer your false assumption:
| Quote: | | It looks like this line would equal zero: |
this three line sequence with integer division equals a MOD:
| Code: | Temp2_long = Temp1_long / 86400
'(a)
Temp2_long = Temp2_long * 86400
Temp1_long = Temp1_long - Temp2_long |
but in difference to the integrated MOD, it allows to pull out the later used integer part all the same:
| Code: | '(a):
Temp3_long = Temp2_long + 36526 |
Your code including MOD does the same division two times by
| Code: | Lcl_long / 86400
Lcl_long Mod 86400 |
as a MOD is an integrated integer division/multiplication with difference building.
My code saves one division.
To answer your question:
| Quote: | | Have you tried the code that you posted? |
Sure I did.
Run it in the simulator and copy the output of PRINT into an appropriate formatted Excel cell. |
|
| Back to top |
|
|
jeremywilson
Joined: 11 Jul 2011
Posts: 79
Location: United States
|
| Posted: Sun Feb 23, 2020 3:08 am Post subject: |
|
|
Got it! Thanks, MWS!
I wasn't considering the decimals truncation in the integer division.
I like your solution. |
|
| Back to top |
|
|
|
FAQ
Search
Register
Log in
