Timestamp wish list (unless somebody knows how to do this)

[rocketman]

I have a situation where creating one flight record can generate up to 4 sales transactions - I won't go into why.

When a flight lands and the button is pressed, these four transactions are created in under 1 second. In actual fact in a separate test I created 10 sales transactions in under 1 second.

So the problem is - if the duty controller misses a flight landing, they have to enter the landing time retrospectively (usually the next day.) . The next problem is, in order for the sales transactions to appear in a member's statement in the correct date and time order, I have to generate a timestamp using TIMESTAMP() based on the date of the previous days flying sheet - and it only allows a resolution of hours and minutes - not hours minutes seconds and milliseconds.

In case anyone disputes a landing time and a flight record needs to be changed, I need to regenerate the sales transaction in the same order they were created so that the opening and closing balances on any forward timestamped transactions can get recalculated correctly. . I NEED milliseconds because even with a resolution of seconds, I will have 4 sales transactions with a timestamp that is not unique- does anyone else need this sort of resolution?

Also - When sorting on a timestamp attribute it seems to sort on the alpha representation of the timestamp rather than the underlying stored integer value - which makes the order of any query inconsistent

It's not a show stopper since I can order by DATE_PART() and ID, but it really would make my code simpler and more elegant if I could simply ORDER BY timestamp attribute

Hope this all makes sense

[tpis001]

So, this problem is really one of finding the correct chronology of these events? Could you latch onto the database ID’s somehow? As these the smallest will be the oldest record and so on.

I might have misunderstood what the problem is though! Thanks...

[rocketman]

its complicated - because a number of things can change, including the identity of the pilots/people to be charged, it's easier and quicker to simply delete all the transactions and regenerate them with the new data , so using ID isn't really an option. If there are two or more flights by the same pilot on the same day and I change the first flight, The ID's will be greater than the last flight.

I have a workaround that increments the minutes by 1 for the second and subsequent transactions but it would be so much easier with a millisecond resolution. Maybe I'm wrong but I always thought that timestamps were held in MySQL in milliseconds anyway.

[Jaymer]

what a bummer

Code: Select all

mysql> CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO fractest VALUES
     > ('17:51:04.777', '2014-09-08 17:51:04.777', '2014-09-08 17:51:04.777');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1          | c2                     | c3                     |
+-------------+------------------------+------------------------+
| 17:51:04.78 | 2014-09-08 17:51:04.78 | 2014-09-08 17:51:04.78 |
+-------------+------------------------+------------------------+
1 row in set (0.00 sec)

Looks like you (MySQL) can do it, but you'd have to recreate your tables to add the extra precision.
Then you'd have to ask Vlad if it would pass the precision through or if he's truncating it.