Timestamp fields in knex.js migrations

Question

I'm currently writing migrations for my database (sqlite3) using knex. I'm making "users" table and I want there to be two timestamp fields, created_at and updated_at. I want them to be notNullable and added by default when inserting a row into table. I can use something like table.timestamp("created_at).notNullable().defaultTo(knex.fn.now()) but in SQLiteStudio it shows as formatted timestamp YYYY-MM-DD HH:MM:SS in UTC timezone (so not my timezone). So I've got 2 questions:

1. How can I set this date to my timezone?
2. Is it possible to set datetime field in sqlite in such way, that when I get it from SELECT statement it is returned as Unix Timestamp (integer) and still be shown as formatted date in SQLiteStudio?

This is my migration 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

Looking 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 times as UTC, then convert to localtime, using strftime() when you want to display the value.

An example using the localtime flag with datetime is given:

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

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

For human readable text you need strftime(). It's a bit gnarly but it works. I'm hoping these sorts of queries are possible from knex, which I've never even heard of!