How is SYS(2015) calculated? 3

[jackieinpa]

I know it's a representation of the system date and time, but how exactly is the value generated? Does anybody know? Thanks!

 

[Tore Bleken]

I'm pretty sure that it's a big secret.

 

[dmusicant]

Yipes! Mine just now was 4KB14GHTR. WTH does that mean?

 

[Olaf Doschke]

If you sort by SYS(2015) indeed they sort in chronological order. But you can't convert these back to the time they were taken. If they were just the time converted to some character set, creating them fast in a tight loop would generate the same values, but you get a series then, eg:

For i = 1 to 10
   ? Sys(2015)
endfor

But in principle it's easily explained, how you increment something not just composed out of numbers, but also letters, hexadecimal numbers are an example for that. And if you include the whole alphabet every posiiotn can hold one of 36 digits 0-9 and a-z.

Bye, Olaf.

 

[Olaf Doschke]

If you'd like to convert this back, without guaranteeing the correctness try this:

https://www.foxite.com/archives/converting-sys2015-back-to-datetime-0000226359.htm

Notice even Cetin says he doesn't know the origin of that code.

You can of course check this with a current SYS(2015) value, but it's no definite proof this is true for all such timestamps. Especially since the function might have a history and different implementations in legacy Foxpro versions. There is no sepcification, as it's not meant to store system time, it's meant to create unique names (on a single system/process only!) sufficiently unique to be used for procedure or cursor and other names (just read the help topic). And as I already said a conversion will at least have a potential error, as that character string is not simply system time, when created more than once per millisecond. That already indicates the number could be a number of milliseconds normally, but VFP makes note of the latest timestamp generated and if systemtime conversion results in the same or a lower value than that, increments its max memorized value instead.

For example genmenu uses it to name some of the menu procedures it generates.

Bye, Olaf.

 

[Mike Lewis]

Welcome to the forum, Jackieinpa. You've asked an interesting question, but I'm curious as to why you want to know the answer. Do you have some special reason for this, or is it just out of curiosity?

I can't add much to what's already been said, except to keep in mind that the value returned by SYS(2015) is not necessarily completely unique, and therefore not suitable for generating unqiue IDs. If two computers running the same application on the same network both call SYS(2015) at the same instant, they will get the same value back (I expect you already knew that).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Even though it could be known, I remember a thread at MSDN forums of somebody actually having used it as primary key and having had a collision. It's seldom, as computer clocks also have an inaccuracy and users are not syncronized, but both these difference can cancel out and doing something just in the correct time lag causes a collision. With syncing of system times via time servers just wait long enough and you get a double key. There's GUID for that matter, I wouldn't fiddle by adding GETENV("COMPUTERNAME") and perhaps ID() and _VFP.ProcessID, the value you generate just get's longer than GUID is.

Bye, Olaf

 

[Mike Gagnon]

A close guess

http://www.atoutfox.org/articles.asp?ACTION=FCONSULTER&ID=0000000052

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Lewis]

That looks very plausible, Mike. But is there any way of testing it? Wouldn't you have to call SYS(2015) and Sys2() in the same millisecond to get comparable results? Then again, maybe that doesn't matter.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, if you do:

For lnI = 1 to 10
? Sys(2015),SYS2(DATETIME())
EndFor

You get a close result, but you get an incrementing SYS(2015) value, while the user defined SYS2 function returns the same result 10 times (unless by pure chance you have a millisecond switch) in this short loop.

To get even closer you could store the computed value somewhere staying in scope and increment it, in case the normal result is equal or lower than the stored result. Replace the stored result otherwise. That way you also don't return the same value twice.

One other thought: If you want to create a compatible function in another language, the major idea is not to encode system datetime in base36, but create a unique name, you could also put together random characters. Always prefix with an underscore to avoid the first char becoming a numeric digit, which renders it an invalid name for generated cursors or variables or procedures, though still usable for file names.

Bye, Olaf.

 

[Olaf Doschke]

And another addition makes it closer in regard of the value:

IF VARTYPE(nMilliSeconds)<>'N'
   nMilliSeconds = Seconds()
   nMilliSeconds = nMilliSeconds-Int(nMilliSeconds)
   nMilliSeconds = nMilliSeconds * 1000
ENDIF

Simple reason: Datetime() has no milliseconds, Seconds() has.

Bye, Olaf.

 

[jackieinpa]

Thank you everybody for the responses. The reason I'm asking is because I am on a project that is converting from a FoxPro application to a Java one. The 15 yr old SQL database has been storing these unique keys and the Java developer would like to do something similar. So he really just needs an algorithm to generate a unique 10 char ID that won't collide with the ones already in the tables. Those all start with "_". The chance of two users generating the same key is very, very low because it's a small company and the users all do different tasks and hit different tables.

 

[Tore Bleken]

I don't know Java, but I'm sure it comes with something similar. Personally I only trust GUIDs, since they are 100% unique always, even across all the computers in the world.

 

[Olaf Doschke]

In that case the keys should rather be converted to GUIDs. Anyway a random string composed of 0-9 and a-z characters would be sufficient, too, if you don't depend on the ascending nature of the keys.

Edit: The current first digits of SYS(2015) (after the underscore) are 4K and that'll stay so for a while, so you could also start your own such keys at "_500000000" and continue with normal numerical digit incrementing, despite of the field staying a character field. That would also distinguish new records from old ones, while being downward compatible and yet more to the point of a primary key sequence. It'll give you 5 billion ids before overflow, more than a 32bit int key and - most important - have no collision with existing keys - you can of course double check that no existing key is >"_500000000".

I'd be quite confident the keys are not inverted to their original system date time, so that characteristic of the key is not needed. Most Foxpro users won't even know these values have an ascending nature.

Bye, Olaf.

 

[Mike Lewis]

I'd also suggest using GUIDs, using something like NEWID() or the equivalent in the target database.

But I can see a potential problem. Given that the existing database is 15 years old, it must already contain a large number of IDs generated by SYS(2015). There is therefore a risk of a collision between these existing IDs and any new ones being generated. Admittedly, that risk is pretty negligible, and it may be safe to ignore it, but it is worth keeping in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[jackieinpa]

Merci, Mike Gagnon, for the link to the code! I had to try to remember my HS French. For anybody else who is interested, when I run this I get the same result as SYS(2015). Thanks everybody for responding

LOCAL lnMilliSecs, lnDays, lcBase36, lcSys2015, lc2Sys2015, ltDateTime, lnSeconds, lnCounter
ltDateTime = DATETIME()
lnSeconds = SECONDS() && Seconds past midnight, with milliseconds, ex. 57878.465
lc2Sys2015 = SYS(2015) && FoxPro's SYS2015 for comparison
lnMilliSecs = lnSeconds * 1000
lnDays=TTOD(ltDateTime)-DATE(YEAR(ltDateTime),1,1)+1+MOD(YEAR(ltDateTime),100)*367
lcBase36='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
lcSys2015=''

FOR lnCounter=1 TO 6
lcSys2015=SUBSTR(lcBase36,INT(MOD(lnMilliSecs,36))+1,1)+lcSys2015
lnMilliSecs=lnMilliSecs/36
ENDFOR

FOR lnCounter=1 TO 3
lcSys2015=SUBSTR(lcBase36,MOD(lnDays,36)+1,1)+lcSys2015
lnDays=INT(lnDays/36)
ENDFOR

lcSys2015='_'+lcSys2015
? lcSys2015
? lc2Sys2015

 

[Olaf Doschke]

Yes, as I already said the milliseconds where the only difference.

I would expect an error when you simply use Seconds()*1000, as you then don't just take the milliseconds, but also 1000*full seconds of that value. Seems to fall down due to the MOD operations, though 1000 modulo 36 is not 0.
BTW, look at ? SYS(3),SECONDS()*1000

I don't want to indicate SYS(3) is the better or more exact value, it's char type, it's just funny the milliseconds since midnight are suggested as good legal temp file name.

Bye, Olaf.

 

[GriffMG]

Errrmmm.

If all the Sys(2015) results begin with _ why not begin yours with a { or ] or anything but _

Or am I missing the point.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

If he wants to differ from the schema that's easy to do indeed. ie go with the best concept and use GUID. Replicating the behaviour is about being fully compatible.
Now since a formula exists, that can also be done in other languages, ie Java.

Bye, Olaf.

 

[dashrader]

Jackie, Nice post, however, it's only unique inasmuch as it's not run in a tight loop. Here are a few modifications that I made so it is unique, always, just like SYS(2015).

Here is a test wrapper for it. Sys2015X is your code. I have modified both mine and yours to accept the date and seconds as parameters so the values will be as close to SYS(2015) as possible. The key to keeping SYS(2015) and MySys2015() in sync is to persist the days and milli seconds, making sure that all subsequent calls to MySys2015() makes sure that the number used for the milli seconds is at least 1 greater than the previous call.

lnIter = 20000
FOR x = 1 TO lnIter
ltDateTime = DATETIME()
lnSeconds = SECONDS()

lc2Sys2015 = SYS(2015) && FoxPro's SYS2015 for comparison
lcMySys2015 = MySys2015(ltDateTime, lnSeconds)
lcSys2015X = Sys2015X(ltDateTime, lnSeconds)
IF x > lnIter - 10

? lc2Sys2015, lcMySys2015, lcSys2015X, lnSeconds
ENDIF
ENDFOR
INKEY(.1)
? "After a short break, MySys2015 is still in sync with SYS(2015)"
FOR x = 1 TO 10
ltDateTime = DATETIME()
lnSeconds = SECONDS()

lc2Sys2015 = SYS(2015) && FoxPro's SYS2015 for comparison
lcMySys2015 = MySys2015(ltDateTime, lnSeconds)
lcSys2015X = Sys2015X(ltDateTime, lnSeconds)

? lc2Sys2015, lcMySys2015, lcSys2015X, lnSeconds
ENDFOR

#DEFINE BASE36 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
#DEFINE MS_IN_DAY 86400*1000
FUNCTION MySys2015(ttDateTime, tnSeconds)
LOCAL lcSys2015, lcMilliSecs, lcDays, ltNow, lnDays, lnMilliSecs

IF VARTYPE(_gnDays) = "U"
PUBLIC _gnDays
_gnDays = 0
ENDIF
IF VARTYPE(_gnMilliSecs) = "U"
PUBLIC _gnMilliSecs
_gnMilliSecs = 0
ENDIF

lcSys2015 = ''
lcMilliSecs = ""
lcDays = ""

*!* ltDateTime = DATETIME()
*!* lnSeconds = SECONDS()
ltDateTime = ttDateTime
lnSeconds = tnSeconds

lnDays = TTOD(ltDateTime) - DATE(YEAR(ltDateTime), 1, 1) + 1 + MOD(YEAR(ltDateTime), 100) * 367
lnMilliSecs = lnSeconds * 1000

DO WHILE _gnDays >= lnDays AND _gnMilliSecs >= lnMilliSecs
lnMilliSecs = _gnMilliSecs + 1
IF lnMilliSecs >= MS_IN_DAY
lnDays = lnDays + 1
lnMilliSecs = 0
ENDIF
ENDDO
_gnDays = lnDays
_gnMilliSecs = lnMilliSecs

FOR lnCounter = 1 TO 6
lcMilliSecs = SUBSTR(BASE36, MOD(lnMilliSecs, 36) + 1, 1) + lcMilliSecs
lnMilliSecs = INT(lnMilliSecs/36)
ENDFOR

FOR lnCounter = 1 TO 3
lcDays = SUBSTR(BASE36, MOD(lnDays, 36) + 1, 1) + lcDays
lnDays = INT(lnDays / 36)
ENDFOR

lcSys2015 = '_' + lcDays + lcMilliSecs
RETURN lcSys2015
ENDFUNC

PROCEDURE Sys2015X(ttDateTime, tnSeconds)

LOCAL lnMilliSecs, lnDays, lcBase36, lcSys2015, lc2Sys2015, lnCounter

*!* ltDateTime = DATETIME()
*!* lnSeconds = SECONDS()
ltDateTime = ttDateTime
lnSeconds = tnSeconds

lnMilliSecs = lnSeconds * 1000
lnDays = TTOD(ltDateTime) - DATE(YEAR(ltDateTime), 1, 1) + 1 + MOD(YEAR(ltDateTime), 100) * 367
lcBase36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
lcSys2015 = ""

IF VARTYPE(gcSys2015) = "U"
PUBLIC gcSys2015
gcSys2015 = ""
ENDIF


FOR lnCounter = 1 TO 6
lcSys2015 = SUBSTR(lcBase36, MOD(lnMilliSecs, 36) + 1, 1) + lcSys2015
lnMilliSecs = INT(lnMilliSecs/36)
ENDFOR


FOR lnCounter = 1 TO 3
lcSys2015 = SUBSTR(lcBase36, MOD(lnDays, 36) + 1, 1) + lcSys2015
lnDays = INT(lnDays / 36)
ENDFOR

lcSys2015 = '_' + lcSys2015

RETURN lcSys2015

ENDPROC