Forum - MCS Electronics

Author Message
jeremywilson

Joined: 11 Jul 2011
Posts: 61
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 )
laborratte

Joined: 27 Jul 2005
Posts: 277
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.
jeremywilson

Joined: 11 Jul 2011
Posts: 61
Location: United States

 Posted: Thu Feb 20, 2020 1:19 am    Post subject: Thanks for the feedback!!
EDC

Joined: 26 Mar 2014
Posts: 729

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
jeremywilson

Joined: 11 Jul 2011
Posts: 61
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.
albertsm

Joined: 09 Apr 2004
Posts: 5199
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
MWS

Joined: 22 Aug 2009
Posts: 1915

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.
jeremywilson

Joined: 11 Jul 2011
Posts: 61
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!
MWS

Joined: 22 Aug 2009
Posts: 1915

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
jeremywilson

Joined: 11 Jul 2011
Posts: 61
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.
jeremywilson

Joined: 11 Jul 2011
Posts: 61
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)
jeremywilson

Joined: 11 Jul 2011
Posts: 61
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
MWS

Joined: 22 Aug 2009
Posts: 1915

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.

 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.

 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.
jeremywilson

Joined: 11 Jul 2011
Posts: 61
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.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT + 1 Hour Page 1 of 1

 Jump to: Select a forum BASCOM AVR/8051----------------BASCOM-AVRBASCOM-8051BASCOM-ARDUINOShare your working BASCOM-8051 code hereShare your working BASCOM-AVR code hereBASCOM BETA-SLA BASCOM Related----------------EASY TCP/IPAVR-DOSAR7212KokkeKat FAT-free SD card libBASCOM Project Blog Other Stuff----------------VariousPCB'sRoboticsNew WebSiteAnnouncementsAVR Archive----------------BASCOM-AVR ArchiveBASCOM-8051 ArchiveBASCOM-AVR Unsupported versionsEasy TCP/IP ArchiveBASCOM-EDB
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum