Datetime with Millisecs 1

[David Higgs]

A Field (BigInt) in a 3rd Party application contains a Value of 20170411165506093

The format is: YYYYMMDDHHMMSSnnn, ie Datetime with Millisecs

How do add millisecs to Datetime?

Regards,

David.

Recreational user of VFP.

 

[Olaf Doschke]

VFP DATETIMES stop at seconds. If you need to preserve that, you need to store it separately. So use a datetime field for the main portion and an integer for milliseconds.

You can't extend the VFP language in extending what native types can store. Like VFP has no bigint, VFP has no such datetime with milliseconds.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

How do add millisecs to Datetime?

David, do you mean you want to add an integer (representing the number of milliseconds) to an existing datetime?

If so, then you can in fact simply add a fraction of a second to a datetime. But although the result will be correct, it will only be stored and displayed to the nearest second.

For example:

tDateTime = DATETIME(2018, 8, 12, 11, 16, 20)
nMillisecs = 1800
tDateTime = tDatetime + (nMillisecs / 1000)   && add 1800 millisecs (1.8 secs) to the datetime
? tDateTime

This will display 12/08/18 11:16:22 (in British date format), which is correct for adding 1800 ms to the original time, rounded to the nearest second. But only the rounded value is stored, so if you do repeated arithmetic on the same value, an error will accumulate. As far as I know, there is no way round that.

Or is your existing datetime in fact a character string, and you want to concatenate the number of milliseconds to the end of the string? In other words, you have a string containing "20170411165506" and you to make it contain ""20170411165506093"?

In that case, it's a simple string manipulation:

cDateTime = cDateTime + PADL(nMillisecs,  3, "0"))

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike, you most likely missed Davids latest thread about SQLite.

A Field (BigInt) in a 3rd Party application contains a Value of 20170411165506093

That's likely an SQLite datetime and it is that value, Mike.
You can only convert the part 20170411165506 to a VFP datetime and store 093 separately if you need to put that back with that precision later.

You could also store this as char(16), of course, and even sort by it, as it's in a sortable format. But if you'd like to display datetimes in your VFP frontend you can only take the precision up to seconds.

Are the milliseconds important at all? Does this database contain records in so fast succession, that you'd reorder the records if you'd only query them into DBF with seconds precision? You could still order the data with your query fetching it from SQLite ORDER BY sqlitedatetime and then have the data in that order and keep it chronological by id or recno or an additional sequence number field without keeping the exact milliseconds.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[David Higgs]

VFP DATETIMES stop at seconds. If you need to preserve that, you need to store it separately. So use a datetime field for the main portion and an integer for milliseconds.

This is the follow on to the SQLite application
[link ]

https://www.tek-tips.com/viewthread.cfm?qid=1788769

[/url] we were discussing.

Are the milliseconds important at all? Does this database contain records in so fast succession, that you'd reorder the records
if you'd only query them into DBF with seconds precision?

The only time this field becomes relavent is if I need to add an entry to the SQLite Table. The SQLIte Field Type is BigInt, and the format yyyymmddhhmmssxxx. The Field is used to record the time of Data Entry, be that Keyboard Input or Time of Data Import. It is the latter that would probably need to have millisec precision. So if i were to add an entry to the SQLite Table, millesec = 000 would probably suffice.

I am just thinking ahead as to what issues I may come across such as how I would handle the Character to BigInt conversion when I try writing back to the SQLite Application. So, I've probably answered my own question.



Regards,

David.

Recreational user of VFP.

 

[Olaf Doschke]

Well,

to write out more than 9 digits into a string is not a big problem, also not in VFP. It's up to the SQLite SQL engine to turn that into a bigint. But I wonder why any application would use bigint as a datetime, in such a manner. I could imagine a redefined Unix time in milliseconds instead of seconds, but not such a number, which needs a large range and has many invalid numbers, eg where day part is 32 or larger.

SQLite has normal types...

https://www.sqlite.org/lang_datefunc.html

So, for example, the SQLite datetime can be fed with a string of format 'YYYY-MM-DD HH:MM:SS', which TTOC(DateTime(),3) comes very close to.

But even simpler, there is ODBC involved and that means no matter what capabilities the remote DB has, you're able to use parameters from the VFP side, that's something VFP handles with the ODBC driver and not the remote database, and since ODBC is a standardized interface you can almost always use VFP native types, even though they are stored completely different, binary, in variables or DBF files or RAM.

Like this:

Local lcDatabaseFilename, lcConnectionString, lnStatementHandle, ldDateTime

lcDatabaseFilename = "d:\temp\sqlite.db"
lcConnectionString = Textmerge("DRIVER=SQLite3 ODBC Driver;Database=<<lcDatabaseFilename>>;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;")
lnStatementHandle = Sqlstringconnect(lcConnectionString)
? lnStatementHandle
? SQLExec(lnStatementHandle,"create table datetimetest (id integer, inserted datetime)")
ldDateTime = Datetime()
? SQLExec(lnStatementHandle,"insert into datetimetest values (1,?m.ldDateTime)")
? SQLExec(lnStatementHandle,"Select * from datetimetest","crsTest")

? SQLDisconnect(lnStatementHandle)
Select crsTest
Browse

Such a parameterization with ?m.varname works with MS SQL Server, MYSQL, SQLite. Becuase VFP only translates that to the ODBC data types, the common format for all databases.

When you don't invent the table schema you can still - as I started saying - put together a string of more than 9 digits without that ever having been a VFP integer, of course. TTOC() has another option to put that string together:

Local lcDatabaseFilename, lcConnectionString, lnStatementHandle, ldDateTime, lcDateTime

lcDatabaseFilename = "d:\temp\sqlite.db"
lcConnectionString = Textmerge("DRIVER=SQLite3 ODBC Driver;Database=<<lcDatabaseFilename>>;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;")
lnStatementHandle = Sqlstringconnect(lcConnectionString)
? lnStatementHandle
? SQLExec(lnStatementHandle,"create table biginttest (id integer, inserted bigint)")
ldDateTime = Datetime()
lcDateTime = Ttoc(ldDateTime,1)+"000"
? SQLExec(lnStatementHandle,"insert into biginttest values (1,"+lcDateTime+")")
? SQLExec(lnStatementHandle,"Select * from biginttest","crsTestBigInt")

? SQLDisconnect(lnStatementHandle)
Select crsTest
Browse

Now AFIELDS helps to see what the ODBC driver comes back with, when you query a bigint to VFP, it simply comes back as char(20). Not ideal, as the type of the field is numeric, but SQLite is forgiving, you can also insert a char(20) into a bigint field. this line works just as good, though it transports the lcDateTime String as a string and not as a number.

? SQLExec(lnStatementHandle,"insert into biginttest values (1,?m.lcDateTime)")

Your mileage might vary, you'll see what arrives when you do such inserts into the already existing database and tables and whether that third party application turns that into correct datetimes for itself.


Last not least instead of +"000" you could also take just the fractional portion of SECONDS(), it doesn't have millisecond precision, but it is, of course, better than "000". Seconds()%1 gives you that fractional part, numerically.

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[David Higgs]

to write out more than 9 digits into a string is not a big problem, also not in VFP. It's up to the SQLite sql engine to turn that into a bugint. But I wonder why any application would use bigint as a datetime, SQLite has normal types...

I too don't understand why the application works this way. This Field, which stores the Datetime that Data is entered or imported, in association with 4 other fields performs the function of a Primary_Key. Similar applications (Logbooks) use a single Primary_Key Field containing an AutoInc INT Value which makes more sense.

Thank you for your Code; that will help me when I start to write to SQLite Database.

So, looking at the rest of the Database, I have all the necessary Data to assist in populating my Front End Module.
Fortunately I have a lot of Code from another module that works with an application that uses MySQL. Once the required Data
is in a VFP Cursor the Procedures will be very similar in both Front End Modules.

Thanks once again for your help.


Regards,

David.

Recreational user of VFP.