Temporary fields in transitions knex.js

Question

I am currently writing migrations for my database (sqlite3) using knex. I am making the users table, and I want to have two timestamp fields, created_atand updated_at. I want them to be notNullable and added by default when inserting a row into a table. I can use something like table.timestamp("created_at).notNullable().defaultTo(knex.fn.now()), but in SQLiteStudio it appears as a formatted timestamp YYYY-MM-DD HH: MM: SS in the UTC time zone (so this is not my time zone). So, I have 2 questions:

• How to set this date in your time zone?
• Is it possible to set the datetime field in sqlite so that when I get it from the statement SELECT, it returns as a Unix timestamp (integer) and still displays as a formatted date in SQLiteStudio?

This is my transition code:

exports.up = function(knex, Promise) {
return Promise.all([
    knex.schema.createTable('users', function(table) {
        table.increments("_id").primary().notNullable();
        table.text("login").unique().notNullable();
        table.text("given_name").notNullable();
        table.text("family_name").notNullable();
        table.timestamp("created_at").notNullable().defaultTo(knex.fn.now());
        table.timestamp("updated_at").notNullable().defaultTo(knex.fn.now());
        table.boolean("is_active").notNullable().defaultTo(true);
    }),
]);
};

Answer 1

A look here should give some clues.

Date and Time Functions

SQLite supports five date and time functions as follows:

date(timestring, modifier, modifier, ...) 
time(timestring, modifier, modifier, ...) 
datetime(timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...) 
strftime(format, timestring, modifier, modifier, ...)

I believe the idea is to always store the time as UTC and then convert to localtime using strftime()when you want to display the value.

localtime datetime:

unix unix 1092941466 .

SELECT datetime(1092941466, 'unixepoch', 'localtime');

strftime(). , . , , !