Comments (10)
@rajkaran Hi again, I didn't quite read your question correctly...
No, I'd use datetime
myself, but I don't think it will change the behavior. As I learned in 2017 there are many, many ways for interactions between local JavaScript Date objects and mysql date/time types to not work the way you're expecting since JSON is the method of communicating with the server it will always need to get serialized and de-serialized so you need to pay attention to GMT offsets on the Loopback server, the mysql server and the local user.
from loopback-connector-mysql.
@ssh24, is it fixed by your recent PRs?
from loopback-connector-mysql.
Can confirm this is not an issue anymore with the latest mysql connector.
Here is a sample reproduction:
model-definition.json
{
"name": "Employee",
"base": "PersistedModel",
"idInjection": true,
"options": {
"validateUpsert": true
},
"properties": {
"name": {
"type": "string"
},
"age": {
"type": "number"
},
"dateJoined": {
"type": "date",
"mysql": {
"dataType": "TIMESTAMP"
}
}
},
"validations": [],
"relations": {},
"acls": [],
"methods": {}
}
boot-script.js
'use strict';
var util = require('util');
var _ = require('lodash');
module.exports = function(app) {
var db = app.datasources.mysqlDs;
var Employee = app.models.Employee;
db.automigrate(function(err) {
if (err) throw err;
console.log('\nAutomigrate completed');
Employee.create([{
name: 'Foo',
dateJoined: new Date()
}, {
name: 'Foo',
dateJoined: new Date('2015-05-25 05:35')
}], function(err, result) {
if (err) throw err;
console.log('\nCreated instance: ' + util.inspect(result));
Employee.find(function(err, result) {
if (err) throw err;
console.log('\nFound instance: ' + util.inspect(result));
});
});
});
};
output
sakibs-mac:mysql-101 ssh$ node .
Web server listening at: http://0.0.0.0:3000
Browse your REST API at http://0.0.0.0:3000/explorer
Automigrate completed
Created instance: [ { name: 'Foo',
dateJoined: Wed Jul 12 2017 10:52:54 GMT-0400 (EDT),
id: 1 },
{ name: 'Foo',
dateJoined: Mon May 25 2015 05:35:00 GMT-0400 (EDT),
id: 2 } ]
Found instance: [ { name: 'Foo',
age: null,
dateJoined: Wed Jul 12 2017 10:52:55 GMT-0400 (EDT),
id: 1 },
{ name: 'Foo',
age: null,
dateJoined: Mon May 25 2015 05:35:00 GMT-0400 (EDT),
id: 2 } ]
Closing this issue as a result. Feel free to reopen if needed.
from loopback-connector-mysql.
I am still facing this issue.
Shift.json
{
"name": "Shift",
"base": "PersistedModel",
"idInjection": false,
"options": {
"validateUpsert": true
},
"properties": {
"id": {
"type": "string",
"id": true,
"defaultFn": "uuidv4"
},
"startDatetime": {
"type": "date",
"mysql": {
"dataType": "TIMESTAMP"
}
},
"endDatetime": {
"type": "date",
"mysql": {
"dataType": "TIMESTAMP"
}
},
"isDeleted": {
"type": "boolean",
"default": false
},
"createDatetime": {
"type": "date",
"mysql": {
"dataType": "TIMESTAMP"
}
},
"modifyDatetime": {
"type": "date",
"mysql": {
"dataType": "TIMESTAMP"
}
},
"isModified": {
"type": "boolean",
"default": false
},
"deleteDatetime": {
"type": "date",
"mysql": {
"dataType": "TIMESTAMP"
}
},
"note": {
"type": "string"
},
"status": {
"type": "string"
}
},
"validations": [],
"relations": {
"staff": {
"type": "belongsTo",
"model": "Staff",
"foreignKey": "staffId"
},
"discipline": {
"type": "belongsTo",
"model": "Discipline",
"foreignKey": "disciplineId"
},
"shifts": {
"type": "hasOne",
"model": "Shift",
"foreignKey": "replacementShiftId"
}
},
"acls": [],
"methods": {}
}
boot-script snippet
function createShift(){
var Shift = app.models.Shift;
Shift.create([{
"createDatetime": new Date(),
"disciplineId": "11017dc8-200a-11e9-94bd-00505689e29d",
"endDatetime": new Date('2015-02-13 03:35'),
"isDeleted": false,
"isModified": false,
"note": "overnight 5-6",
"staffId": "75bfd6c7-2589-11e9-94bd-00505689e29d",
"startDatetime": new Date('2015-02-12 23:35'),
"status": "confirm"
}], function(err, result) {
if (err) throw err;
console.log('\nCreated instance: ' + util.inspect(result));
Shift.find(function(err, result) {
if (err) throw err;
console.log('\nFound instance: ' + util.inspect(result));
});
});
}
DB table
CREATE TABLE `Shift` (
`id` varchar(255) NOT NULL,
`startDatetime` timestamp NULL,
`endDatetime` timestamp NULL,
`isDeleted` tinyint(1) DEFAULT NULL,
`createDatetime` timestamp NULL,
`modifyDatetime` timestamp NULL,
`isModified` tinyint(1) DEFAULT NULL,
`deleteDatetime` timestamp NULL,
`note` varchar(512) DEFAULT NULL,
`status` varchar(512) DEFAULT NULL,
`staffId` varchar(512) DEFAULT NULL,
`disciplineId` varchar(512) DEFAULT NULL,
`replacementShiftId` varchar(512) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Result
Found instance: [ Shift {
__cachedRelations: {},
__data:
{ id: '8e8e0b00-13ab-401e-9c05-00fabecfc56a',
startDatetime: 2015-02-13T04:35:00.000Z,
endDatetime: 2015-02-13T08:35:00.000Z,
isDeleted: false,
createDatetime: 2019-02-06T20:29:00.000Z,
modifyDatetime: null,
isModified: false,
deleteDatetime: null,
note: 'overnight 5-6',
status: 'confirm',
staffId: '75bfd6c7-2589-11e9-94bd-00505689e29d',
disciplineId: '11017dc8-200a-11e9-94bd-00505689e29d',
replacementShiftId: null },
__dataSource: undefined,
__strict: false,
__persisted: true } ]
What I am doing wrong. This is effecting my queries especially when shift is overnight.
[email protected]
[email protected]
from loopback-connector-mysql.
@rajkaran what is your local GMT offset?
Do you get back the same startDatetime
('2015-02-12 23:35') if you run console.log(new Date("2015-02-13T04:35:00.000Z").toString());
?
from loopback-connector-mysql.
local GMT offset is -5:00
console.log will return right timestamp, but when I am making query on timestamp field I do not get expected results
for instance
Shift.find({where: {startDatetime: {gt: '2015-02-12 03:35:00'} })
this query will not exclude shifts starting 2015-02-11 23:35:00. because of UTC timestamp stored in mysql database.
from loopback-connector-mysql.
Can you do something like:
var startDatetimeObj = new Date('2015-02-12 03:35:00');
Shift.find({where: {startDatetime: {gt: startDatetimeObj}}});
from loopback-connector-mysql.
Yes I can do that..
Does startDatetime field in database needs to be of timestamp datatype. Automigrate function creates it of datetime type.
from loopback-connector-mysql.
@rajkaran I'm afraid I don't know, I've been away from Loopback for a while, but I was involved with trying to fix some serious bugs in the date
, datetime
and timestamp
handling back in 2017. I hope it is working reliably now, but I don't have specific experience with either timestamp
or Automigrate. I was using Loopback with an existing mysql DB and finding much trouble dealing with existing date
and datetime
columns. I think that with the help of many others we fixed it back in 2017 and want to make sure it is still working...
#149
from loopback-connector-mysql.
@bbito No problem... 👍
Thank you for your assistance.
from loopback-connector-mysql.
Related Issues (20)
- Support for Full Text Search
- AWS EC2 LoopBack Keep Connecting to 127.0.0.1 Even Already Specified RDS Endpoint HOT 6
- Tests are failing HOT 3
- Default Function UUID inconsistence HOT 1
- Connection to database hangs after Error: read ETIMEDOUT HOT 2
- Model's ID not set as private key in MySQL database
- RAW Query fails when server and DB are on differents IPs
- Fulltext index on a model property is not migrated to the datasource
- mysql.index is not work on property
- compatibility issue with mysql aurora 8.0.* using regex HOT 1
- n-gram specification on FULLTEXT index HOT 2
- Mapping of type blob/longblob to Binary fails the lb4 discover command HOT 1
- Fix CI pipeline HOT 1
- FULLTEXT | MYSQL | failed with status code 500. Error: ER_PARSE_ERROR: You have an error in your SQL syntax; HOT 1
- Add support for nodejs v18 and mysql v8 HOT 5
- Empty body on update generates an MySQL error HOT 2
- Support Latest Node 18 (LTS) HOT 1
- Dependency Dashboard
- Remove unknown options to the mysql2 initalizer HOT 3
- empty string is converted to empty object in LoopBack3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from loopback-connector-mysql.