Forum - MCS Electronics

 

FAQFAQ SearchSearch RegisterRegister Log inLog in

system time conversion

 
Post new topic   Reply to topic    www.mcselec.com Forum Index -> BASCOM-AVR
View previous topic :: View next topic  
Author Message
jeremywilson

Bascom Member



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

usa.gif
PostPosted: Thu Feb 20, 2020 12:21 am    Post subject: system time conversion Reply with quote

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
View user's profile AIM Address
laborratte

Bascom Expert



Joined: 27 Jul 2005
Posts: 260
Location: Berlin

germany.gif
PostPosted: Thu Feb 20, 2020 1:16 am    Post subject: Reply with quote

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
View user's profile
jeremywilson

Bascom Member



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

usa.gif
PostPosted: Thu Feb 20, 2020 1:19 am    Post subject: Reply with quote

Thanks for the feedback!!
Back to top
View user's profile AIM Address
EDC

Bascom Expert



Joined: 26 Mar 2014
Posts: 677

poland.gif
PostPosted: Thu Feb 20, 2020 1:44 am    Post subject: Reply with quote

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 Very Happy
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
View user's profile Visit poster's website
jeremywilson

Bascom Member



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

usa.gif
PostPosted: Fri Feb 21, 2020 6:08 pm    Post subject: Reply with quote

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
View user's profile AIM Address
albertsm

Administrator



Joined: 09 Apr 2004
Posts: 5016
Location: Holland

blank.gif
PostPosted: Fri Feb 21, 2020 9:52 pm    Post subject: Reply with quote

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
View user's profile Visit poster's website
MWS

Bascom Member



Joined: 22 Aug 2009
Posts: 1803

blank.gif
PostPosted: Fri Feb 21, 2020 10:41 pm    Post subject: Reply with quote

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
View user's profile
jeremywilson

Bascom Member



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

usa.gif
PostPosted: Fri Feb 21, 2020 11:07 pm    Post subject: Reply with quote

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
View user's profile AIM Address
MWS

Bascom Member



Joined: 22 Aug 2009
Posts: 1803

blank.gif
PostPosted: Sat Feb 22, 2020 4:17 pm    Post subject: Reply with quote

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
View user's profile
jeremywilson

Bascom Member



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

usa.gif
PostPosted: Sat Feb 22, 2020 5:14 pm    Post subject: Reply with quote

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
View user's profile AIM Address
jeremywilson

Bascom Member



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

usa.gif
PostPosted: Sat Feb 22, 2020 5:27 pm    Post subject: Reply with quote

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
View user's profile AIM Address
jeremywilson

Bascom Member



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

usa.gif
PostPosted: Sat Feb 22, 2020 6:22 pm    Post subject: Reply with quote

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
View user's profile AIM Address
MWS

Bascom Member



Joined: 22 Aug 2009
Posts: 1803

blank.gif
PostPosted: Sat Feb 22, 2020 8:13 pm    Post subject: Reply with quote

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
View user's profile
jeremywilson

Bascom Member



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

usa.gif
PostPosted: Sun Feb 23, 2020 3:08 am    Post subject: Reply with quote

Got it! Thanks, MWS!
I wasn't considering the decimals truncation in the integer division.
I like your solution.
Back to top
View user's profile AIM Address
Display posts from previous:   
Post new topic   Reply to topic    www.mcselec.com Forum Index -> BASCOM-AVR All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
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
You cannot download files in this forum