Convert created_at value to another timezone before saving

I have a query to how to convert the created_at date value to any specific timezone (ex. UTC) while saving the new record.

  • I am using the increments/timestamp to create the created_at field in the database. The values of created_at and updated_at fields will be generated according to the server timezone by the adonis. But i need to change the timezone of the created_at value before saving the record.

  • I have already tried a solution found in knex.js but it is also not working.

connection: {

timezone: ‘UTC’,
typeCast: function (field, next) {
if (field.type == ‘DATETIME’) {
return moment(field.string()).format(‘YYYY-MM-DD HH:mm:ss’);
}
return next();
}
}

Thanks in advance!

2 Likes

Up! I’ve tried with adding next pool value to the connection object in config/database.js and it’s not working:

 pool: {
        afterCreate: function(connection, callback) {
          connection.query('SET time_zone = "UTC+4";', function(err) {
            callback(err, connection);
          });
        }
      }

i’ts also funny because adding " timezone: ‘-4:00’ " right before pool at the same connection objetct… register started to change created at, and did some time traveling hahaha Captura%20de%20pantalla%20de%202019-08-14%2003-04-07

Please help! Is there any configuration, maybe at Lucid models or somewhere else, where we can set the timestamps to desired timezone?

1 Like

Why not use dateformats https://adonisjs.com/docs/4.1/lucid#_formatting_date_fields

1 Like

Hi @virk, can you provide some example on how to change the timezone using dateformats?

I also encountered similar issue that when the server timezone is not UTC but DB is. For example UTC+8 in my case, when a record created or updated, adonis try to save localtime to the DB while database (postgres field with timestamptz type in my case) think this time is UTC. In later queries on those datatime field, looks like adonis will check the timezone and automatically correct it (by +8).

The funny time traveling issue also happened to me that in each update the created_at field will be +8 hours.

It is strange to me that if adonis will check and convert the timezone (+8) to localtime after query, why not it do the same thing (-8) before insert to the DB. Or is there and config can tell adonis the timezone of DB so that it knows there is timezone difference?

1 Like

Hi @virk, thank you for pointing me out direction. I solved the issue. However, just let you know that the document about formatDates() is not correct as I posted in https://forum.adonisjs.com/t/how-to-use-format-in-adonisjs/3230/9

Anyway, the solution was to convert all date field to UTC via

class User extends Model {
  static formatDates(field, value) {
    return new Date(value).toISOString();
  }
}

It even not required to use moment, the javascript Date object already contains the timezone information and what we need to do is to convert it to UTC string that DB recognize.

Note that the value in formatDates is not a moment object (where the document is wrong). If someone want to use moment, use something like

return moment(value).utc().format('YYYY-MM-DDTHH:mm:ss.SSS[Z]')
2 Likes

it worked for me. thank you friend. :risonho: