Timestamp milliseconds in MySQL

Archived Topics
1

I’m reading that MySQL does not support fractional seconds in the Timestamp data type. Is this true?

Is there any guidance you can offer to work around this shortcoming to display fractional second data in a chart? Thanks.

2

Ah yes, I remember clearly the crushing feeling of defeat when I first discovered this shocking shortcoming of MySQL.

Of course, we aren’t alone, see this google search. for lots of information about this issue.

Your best hope for a workaround is to store the date in a DATETIME field, and then have a separate integer column for microseconds, and to select the date, do a

SELECT DATE_ADD(t_stamp, INTERVAL microsec_val MICROSECOND) FROM ...

Or - switch to SQL Server, Oracle, DB2, etc, etc.

Don’t get me wrong, MySQL is a great database, but this is a really annoying bug.

Good luck,

3

So that SELECT statement will produce a DATETIME with fractional seconds, and FPMI can deal with it (just as if it came from a database that supported this). Correct?

Thanks. That should work for me.

4

I’m pretty sure. You’re going to have to TIAS (try it and see).

5

I presume this means I don’t get to use EasyChart, but will construct a dataset to fill an old-style chart. True?

6

no, DATE_ADD should return a proper date object that the easy chart can use. You just have to set your x-column values on your pens to the DATE_ADD() function, rather than the straight T_STAMP. Or better yet, make a view.

7

All very cool. I got the view working, and the chart working using the view.

The only thing that’s odd about it is this.

There is 30 seconds worth of data at 100 msec intervals - 300 data points. It appears that the x axis resolution is 1 second, so when data is changing (and my pen is plotting points), there is a stack of 10 points every second.

It only gets weirder from here. I created a database view to be able to use the fractional seconds in my timestamp in MySQL. In a MySQL query browser, when I SELECT all the data from the view and ORDER BY the artificial, fractional-second timestamp, the timestamp data is ordered like this.

0.0, 0.9, 0,8, 0.7, … 0.2, 0.1, 1.0, 1.9, 1.8 etc

Apparently that’s also the way the chart orders the data because, when I plot the pen using a line, it connects the wrong ends of the stacks of data, creating a sawtooth effect. I.e. on descending data, the top of the first stack is connected to the bottom of the second stack, etc - sawtooth.

So, some questions:

  1. Can the resolution of the x-axis be changed?

  2. Can the data be ordered by something other than the timestamp used as the x-axis values. (I have the original unit-second timestamp and the fractional seconds as separate fields in my view, it there’s some way to order by them.)

  3. Why is the data being sorted this way in the first place?

Thanks for any help.

8

  1. The x-axis resolution is to the millisecond. Your fractional second data must not be coming through JDBC correctly. ( you could try upgrading the MySQL JDBC driver )

  2. No, it always orders by the timestamp.

  3. Thats a good question, and the one that you have to fix, although #1 might be a bigger blockage.

There was a reason I signed my first reply to this thread with “Good luck”, now you’re seeing why.

(its not too late to install MS SQl Server Express for your historical data!)

Good luck.

9

  1. Re the x-axis resolution, is there any way to view the dataset that is being plotted?

  2. I have no idea if it would ever be used (except now by me) but it would be nice to have the option of a non-standard ORDER BY clause.

  3. I’ll noodle a little with this one.

Sadly, it is indeed too late for a database change. The boss’s demo is within the next hour or so, and he probably wouldn’t appreciate it if something happened and I blew the whole thing up.

Again, thanks for all the good help.

10

  1. nope.

  2. I suppose, although I can’t think of any legitimate use other than getting around this limitation.

Heres a thought - since this is for a demo could you just have 1 second chart resolution?

Your only course of action at this point is to use the classic chart, and to manipulate the timestamps in Jython before you feed the data into the chart.

11

  1. I queried from the VIEW into a table and, indeed, the timestamp doesn’t include fractional seconds. Bummer.

  2. No, I can’t think of any other use either, but since I’m sure your programmers are looking for work to do…

As for the demo, he’ll just have to live without this particular screen. No big problem. Thanks again.

12

bds,

I know this may sounds like a stupid question, but what is your order by clause for the view you’ve created? If my list was in this order,[quote]0.0, 0.9, 0.8, 0.7, … 0.2, 0.1, 1.0, 1.9, 1.8 etc [/quote]I would assume I hadORDER BY t_stamp ASC, microsec_val DESC Also, if FactorySQL is logging this data, couldn’t you just order by the index?

13

[quote=“Robert.McKenzie”]bds,

I know this may sounds like a stupid question, but what is your order by clause for the view you’ve created? If my list was in this order,[quote]0.0, 0.9, 0.8, 0.7, … 0.2, 0.1, 1.0, 1.9, 1.8 etc [/quote]I would assume I hadORDER BY t_stamp ASC, microsec_val DESC Also, if FactorySQL is logging this data, couldn’t you just order by the index?[/quote]

This data is coming into the easy chart, which forces an order by using the timestamp column.

14

No DESC in the query. Also note that it is 0.0 then 0.9, etc, not descending order.

Also, the true order is

0.0, 0.9, 0.9 … 0.1, 1.9, 1.8 the descending within the 1.x, then descending within the 2.x, etc.

Mysterious.