Why does my post request to PostgreSQL DB save my UTC DATETIME as a local time

Question

I'm passing an object to my Sails API that has two properties with values of a date-time string(2015-05-12 13:30:00+00") and a time string("7:00 AM") respectively.

In my Sails controller I remove the original time from the date-time string, convert the time string to a 24hr format and create a UTC date object to insert in my PostgreSQL database. Here is the controller code;

module.exports = {
   toDateObj: function(req, res) {
      if (!req.param('jobSchedObj')) {
        res.badRequest('Missing required parameter!');
      } else {
        var date = req.param('jobSchedObj').scheduled_date;
        var startTime = req.param('jobSchedObj').scheduled_start_time;
        var endTime = req.param('jobSchedObj').scheduled_end_time;
        var times = {scheduled_start_time: startTime, scheduled_end_time: endTime};
        var toInsert = [];
        var toInsertObj = {};
        for (var time in times) {
        //Strip time from date
        var dateRegex = /(\d{4})-(\d{2})-(\d{2})/g;
        var thisDate = dateRegex.exec(date);

        //format new time to 24-hour format
        var hours = Number(times[time].match(/^(\d+)/)[1]);
        var minutes = Number(times[time].match(/:(\d+)/)[1]);
        var AP = times[time].match(/\s(.*)$/);
        if (!AP) {
          AP = times[time].slice(-2);
        } else {
          AP = AP[1]
        }
        if (AP == "PM" && hours < 12) {
          hours = hours + 12
        }
        if (AP == "AM" && hours == 12) {
          hours = hours - 12
        }
        var Hours24 = hours.toString();
        var Minutes24 = minutes.toString();
        if (hours < 10) {
          Hours24 = "0" + Hours24
        }
        if (minutes < 10) {
          Minutes24 = "0" + Minutes24
        }

        //Concat new 24-hour time with date and create Date Obj   
        toInsertObj[time] = new Date(Date.UTC(parseInt(thisDate[1]), 
          parseInt(thisDate[2]) - 1, 
          parseInt(thisDate[3]), 
          parseInt(Hours24), 
          parseInt(Minutes24)));

    }
        toInsertObj.allotted_time = req.param('jobSchedObj').allotted_time;
        toInsertObj.repair_shop_equipment_id = req.param('jobSchedObj').repair_shop_equipment_id;
        toInsertObj.technician_id = req.param('jobSchedObj').technician_id;
        toInsertObj.repair_history_id = req.param('jobSchedObj').repair_history_id;
        toInsertObj.repair_shop_id = req.param('jobSchedObj').repair_shop_id;

        toInsert.push(toInsertObj);

        Schedules.create(toInsert, function (err, schedules){
          if (err) {
            res.send(err);
          } else {
            res.send(schedules);
          }
        }); 
      }
    }
 };

And here is a CodePen where I test to see what my controller logic is doing.

If I input something like my original example at the top "2015-05-12 13:30:00+00", "7:00 AM" it returns Tue May 12 2015 03:30:00 GMT-0400 (EDT) which is UTC.

But if I open the table in pgAdmin It saves as 2015-05-12 7:30:00+00 and when I make a get request to retrieve the value it brings it back as a string "2015-05-12T7:30:00.000Z"

Then no matter what I try to do to change that string back to a date object the date value ends up equivalent to 2015-05-12 3:30 AM. I've tried Date.parse(<string>), new Date(<string>) as well as Moment.js's various methods. Even cutting off the 000Z at the end of the return string before parsing it made no difference.

What I want to return is something I can convert to an epoch time that represents the either UTC/local of the time posted.

This is NOT an answer to your question, but it might be helpful. I have found bouncing from framework to framework and DB to DB that I now store all my dates as INT in the DB and then transform them back into dates on the server / client using libraries like Moment.js. This has helped me a lot because dates can be manipulated at the DB level and the DB connection level (adapter), at the server and then on the local client depending on if your using a js framework to display the dates. Eventually I got fed up and found by saving as INT solved all these hard aches. — Meeker, May 17 '15 at 16:45

score 2 · Answer 1 · answered May 17 '15 at 21:29

Brad, sails-postgresql has timezone issues, check balderdashy/sails-postgresql#153. Associated with it I've changed the travis ci build to run the sails-postgresql standard tests in the Netherlands timezone and that made the tests break: https://travis-ci.org/balderdashy/sails-postgresql/jobs/59237954#L263.

If I input something like my original example at the top "2015-05-12 13:30:00+00", "7:00 AM" it returns Tue May 12 2015 03:30:00 GMT-0400 (EDT) which is UTC.

But if I open the table in pgAdmin It saves as 2015-05-12 7:30:00+00

sails-postgresql (through waterline-sequel) converts dates to UTC before sending them to PostgreSQL (relevant code) which may explain what you see in pgAdmin. More details about sails-postgresql behaviour in this comment.

There is a broader discussion in balderdashy/sails-postgresql#153 to figure out how to handle dates in waterline and how sails-postgresql should be changed to fit that.

Thanks @Dário, I've linked this post up with [balderdashy/sails-postgresql#153](https://github.com/balderdashy/sails-postgresql/issues/153). I'm at a stand still until this can be resolved. If there is something I can do to help get to a solution faster I will. But it seems like the problem is known and it's just a discussion about what direction the solution takes. — Brad W, May 18 '15 at 14:12
You're welcome. It's a painful problem since whatever change is implemented it can't break behaviour for existing users since people may have already coded around the issue. Ideally, a solution should allow (through config) to revert back to the current behaviour. — Dário, May 18 '15 at 14:44

Why does my post request to PostgreSQL DB save my UTC DATETIME as a local time

1 Answers1