TIME improperly converted to invalid date

[John Gwinner]

Before I write this up as a bug, per the instructions, I thought I'd check here.  This is really about the MySQL connector.

TIME, as a database field, represents not a date, but an amount of time (elapsed time, etc). We are using it to record the duration of a medical event.

However, when building an Angular client (by hand due to the absolutely absurd SDK issues), I noticed Loopback converts this to a Date. That wouldn't be so bad, IF it was a valid date, and consisted of the amount of seconds or milliseconds since midnight or something.

This almost seems to work, for example, 4 seconds ends up being '0000-00-00 00:00:04'

If I try to convert that to say seconds, I get an error:

{{camDetection.recDuration | date: 'hh:mm:ss'}}



ERROR Error: "InvalidPipeArgument: 'Unable to convert "0000-00-00 00:00:04" into a date' for pipe 'DatePipe'"

I mean, all I want it to do is display the last four digits. I realize I could build a custom pipe to do it, but it looks to me like something is fundamentally wrong with the time to date conversion.

I also tried the ngx-moment module, but it always says "A few seconds" even for 4 minute incidents. We really could use the exact time.

Can anyone help?

        == John ==

[John Gwinner]

Well, I figured I'd try a non 0 time, but still get an error:

ERROR Error: "InvalidPipeArgument: 'Unable to convert "0000-00-00 00:00:01" into a date' for pipe 'DatePipe'"

1 second is ABSOLUTELY a valid TIME value. It should convert to a JavaScript date of something close to 1970-01-01 00:00:01 

        == John ==

[Buck Bito]

Hi John,

I can't get JavaScript to make a valid date from a string like "0000-00-00 00:00:01".

Can you set your loopback model to handle your MySQL TIME field as a String and build your date object on the client-side using a hard-coded date portion?

e.g.:

var datePortion = '2000-01-01T';
var assembledDate = new Date(datePortion + mysqlTimeStr);

For example this will produce "Sat Jan 01 2000 00:00:04":

var datePortion = '2000-01-01T';
var timePortion = '00:00:04';
var assembledDate = new Date(datePortion + timePortion);

[Buck Bito]

Hi John,

I'm really not sure why you're ending up with your MySQL TIME field being cast to a Date object.I spent a fair amount of time digging into Date issues in both Loopback and the underlying mysqljs/mysql library and my recollection is that TIME is not by default cast to a Date object by either mysqljs/mysql or loopback-connector-mysql.

Only DATE, TIMESTAMP and DATETIME should default to a Date object casting.

TIME should default to a String in both mysqljs/mysql and loopback-connector-mysql

Can you say how your Loopback model was created and post a sample?

Loopback 3 or 4?

On Thursday, February 7, 2019 at 2:03:45 PM UTC-8, John Gwinner wrote:

Before I write this up as a bug, per the instructions, I thought I'd check here.  This is really about the MySQL connector.

TIME, as a database field, represents not a date, but an amount of time (elapsed time, etc). We are using it to record the duration of a medical event.

...

        == John ==

[John Gwinner]

Hey, thanks for the response Buck!

>>I can't get JavaScript to make a valid date from a string like "0000-00-00 00:00:01".<<

Yup, that was the issue.

I'm not building a DATE object unless you mean as a workaround.

I can't remember where the 0000-00-00 00:00:01 came from; that was part of the issue. It's just what popped out in the error.

Where I was trying to use TIME in the database I just reconstructed it to have a DATETIME start and a DATETIME end. :) So that avoids the problem. I wasn't finding an answer (I realize this isn't a paid support forum) so I just changed the database. 

To build the Loopback models, we're using a small JS program that uses discoverSchemas. 

It's kind of frustrating that SQL has more robust datatypes. Once you convert a TIME object into a DATE you start having to deal with weird issues; for example, 25 hours suddenly ends up being 1 hour (plus a day). I realize this is just the way it is :)

There's one other area where we have durations, but those TIME values are coming over OK as strings. I forget what database type I had the "recDuration" as. Ok, I checked git and it was a DATETIME. The MOTION software was storing the TIME, but something got 'off' in the conversion. JavaScript time is stored as the number of milliseconds from 1970 (etc) so maybe something got weirded out there, and Loopback coughed. I just had MOTION store the complete date and time.

When DATES are actually handled as DATES (and TIME) and not as strings, suddenly Y2K issues go away. We've forgotten the pain!

        == John ==

[John Gwinner]

Well, I spoke too soon. Everything displayed fine (the duration is a read only value), but when I pass the string TIME back through Looopback, I get the following error:

unhandled error { Error: ER_TRUNCATED_WRONG_VALUE: Incorrect time value: 'string' for column `test_db`.`incidents`.`duration` at row 1

I'll just get rid of TIME everywhere in the database. passing in the exact string that was passed out SHOULD work. The field involved was a read only value, and we did no processing on it.

        == John ==

[Buck Bito]

Hi John,

Do you know where that error is being generating? I don't see it in the datajuggler, loopback-connector-mysql, or mysqljs/mysql - maybe I'm missing something or this is from another library you're working with? I can't find any specific support for TIME in any of the loopback / mysql modules...

I think you're fine working with your TIME fields, but you'll need to write your own transforms in JavaScript to turn the Strings into something you can work with and back to Strings again. There is no native JavaScript object that corresponds to MySQL TIME fields.... I don't have familiarity with Date/Time libraries like Moment.js, but perhaps that would take care of such transformations... 

[Buck Bito]

FWIW, I think you're best off either using start/end DATETIMEs or using a float or integer field to store duration as fractional hours, minutes or seconds. JavaScript and JSON can handle either of those forms without having to transform to/from hhh:mm:ss.x strings.

-- Sent from my HP Pre

---

--
You received this message because you are subscribed to a topic in the Google Groups "LoopbackJS" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/loopbackjs/qSEPO2UMva8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to loopbackjs+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/loopbackjs/5d53b82a-5d01-4179-9909-1de90868d78c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.