Manually building a timestamp with resolution in millisecond

rocketman

Im trying to remove a vey processor intensive overnight process. I know what I want to do which is all to do with allowing clients to insert sales transaction at a previous date to the current date. It should be easy but when I try to manually build a TIMESTAMP from the Tax Point date, I only seem to be able to build it to a resolution of minutes (not seconds or even milliseconds.)

The problem is entering one flight generates two (sometimes 4) transactions one for the launch fee and one for the airtime (4 if the pilot and co-pilot want to split the cost) and modern processors are so fast that they are all being written in the same second. That makes the next stage of the operation difficult - which is to ripple through all the forward transaction to adjust the opening and closing balances for the "Statement of Accounts" With new transactions on the current date it's not a problem because CURRENT_TIMESTAMP records in milliseconds and I hold the timestamp of the last transaction in the customer (member) file so I always know when I can use CURRENT_TIMESTAMP and when I have to try to build the timestamp on the fly

Any mathematical geniuses out there who can figure a way round this - it would be appreciated.

rocketman

OK - Getting closer

DateTester.DateAsString=AS_STRING(DateTester.AnyDate) Returns 2017-06-09
DateTester.TimeAsString=CHARS_FROM_RIGHT(AS_STRING(CURRENT_TIMESTAMP),12) returns 15:15:24.086 just what I need (millisecond resolution)

DateTester.TheTimeStamp=AS_TIMESTAMP(AS_STRING(DateTester.AnyDate) + ' ' + CHARS_FROM_RIGHT(AS_STRING(CURRENT_TIMESTAMP),12),'dd/MM/yyyy HH:mm.ss') returns 06/09/2017 15:15:24

Which is ok providing Sorting by this attribute ascending will take into account the milliseconds.

Can't figure out how to define the timestamp format string to show the milliseconds

BLOMASKY

I don't know what database you are using, but could you have a trigger that updates a field with the date/time to the millisecond. (or, it could be a calc with the # of milliseconds past midnight). Then when you retrieve the record it will have the correct precision.

Bruce

rocketman

It's Mysql.

I've got the resolution i need and I know its in the timestamp even though I can't display it because I created another attribute that grabbed the last 12 characters from the datetester.thetimestamp field

DateTester.TWITS_String=CHARS_FROM_RIGHT(AS_STRING(DateTester.TheTimeStamp),12) returns 15:15:24.086

What's weird is If I sort TheTimeStamp in descending order they come out right. If I change it to ascending - they are wrong .
For the test I created 10 records using a FOR EACH construct in under 1 second

Getting bleary eyed so might be missing something Will look again tomorrow