Additinal information:

field [ETA] was retrieve from other MySQL DB (called instance 'A') and the type of data is 'time'. The value in instance 'A' for this field is in format 'HH:mm:ss', such as '23:59:00'.

After this field [ETA] was imported to Omniscope, the value for this field change to this format '01-Jan-1970 23:59:00'

So, when we tried to export this field [ETA] to other MySQL DB (instance 'B'), the type of data was changed to be 'timestamp' always. We manually adjusted type of data to be 'time' and the value in Omniscope to be 'HH:mm:ss' but we still have this error.

Mon - A Date/Time field being imported into Omniscope will revert to displaying the 01-Jan-1970 month/year value (the basis for Epoch/Unix/Posix time) if the full, valid date value (including year) is NOT being read in from a single field. Omniscope expects the entire value specifying a point in time: years down to milliseconds, to be imported from a single field as a single valid value.

Spreadsheets often store the Dates and the Times in separate fields, resulting in Omniscope importing just the the time and having to 'guess' the year. If possible, set your database to store the entire date/time value as a single value (usually milliseconds since 01 January 1970). If this is not possible, import the multiple fields as Text and combine the fields as Text, then use the TEXTTODATE function to convert to a fully specified single-field Date/Time values.

Thomas, we understand your comment and advice above, however we are experiencing a further anomaly even when we are publishing a combined DATETIME field to mySQL.

The Field received by mySQL contains only the Date. The Time field has gone to 00:00:00

We can fix the former problem by joining DATE and TIME fields as you suggest but we cannot see how to preserve the TIME.

This suggests there may be some format error between Omniscope Publish and mySQL?

Additional:

The date/time data (timestamp type) is set to '12-Mar-2012 18:13:59' but when we publish it back to MySQL (timestamp type) it shows only '12-Mar-2012 00:00:00'. The time is missing after we publish it back.

Are you certain the time elements are non-zero in the preview of the input block to the publish block?

Yes, we could see the time in the input block before publish as '12-Mar-2012 18:13:59'. Our version is 2.7 build 289.

I tried to capture the screen to show you but when I published the DB again. It showed the error as attached - -'

Please find more screenshot here ^-^

Hi,

We will investigate this issue and get back to you.

Hi,

I am also having the 00:00:00 time issue when converting to a MySQL timestamp. I have the date/time formatted as yyyy-MM-dd HH:mm:ss within Omniscope and I have verified that it appears correctly in the Preview pane. I am running version 2.7 build 299 (beta).

Any help would be much appreciated! Please let me know if I can provide any other useful information.

Cheers, Alec

Hi,

This issue occurs because at the moment the Omniscope Database publisher does not support writing as a Timestamp. We are looking to implement this very soon, however it will probably only be available in Omniscope 2.8. Omniscope 2.8 Alpha should be available to download in the next few weeks.