Giter VIP home page Giter VIP logo

Comments (10)

bbito avatar bbito commented on August 16, 2024 1

@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.

dhmlau avatar dhmlau commented on August 16, 2024

@ssh24, is it fixed by your recent PRs?

from loopback-connector-mysql.

ssh24 avatar ssh24 commented on August 16, 2024

Can confirm this is not an issue anymore with the latest mysql connector.

[email protected]

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.

rajkaran avatar rajkaran commented on August 16, 2024

@ssh24

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.

bbito avatar bbito commented on August 16, 2024

@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.

rajkaran avatar rajkaran commented on August 16, 2024

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.

bbito avatar bbito commented on August 16, 2024

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.

rajkaran avatar rajkaran commented on August 16, 2024

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.

bbito avatar bbito commented on August 16, 2024

@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.

rajkaran avatar rajkaran commented on August 16, 2024

@bbito No problem... 👍

Thank you for your assistance.

from loopback-connector-mysql.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo 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.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.