CODEWITHSUNDEEP
node.jsknex.js
user2473015
user2473015

Reputation: 1442

Knex get formatted time from a time column and date column

I have a column defined with type time and column defined with type date.

knex.schema.createTable('receipt', function(table) {
...
    table.date('t_date');
    table.time('t_time');
...
});

It stores data in 24hour format in Database. eg: 22:00:00. Date as '2018-11-30'. But when I run a select query it returns.

{
...
    "transactionDate": "2018-11-29T00:00:00.000Z",
    "transactionTime": "1970-01-01T22:20:00.000Z",
...
}

How to get date from select query with date with format YYYY-MM-dd and time as HH:mm:ssAM/PM?

Upvotes: 5

Views: 23973

Answers (5)

M. Hamza Rajput
M. Hamza Rajput

Reputation: 10276

This solution is working fine for me with PostgreSQL 12.

Moment will also resolve the timezone.

const knex = require('knex');
const moment = require('moment');
const { setTypeParser, builtins } = require('pg').types;

setTypeParser(builtins.DATE, val => moment(val).format('YYYY-MM-DD'));
setTypeParser(builtins.TIME, val => moment(val).format('HH:mm:ss'));
setTypeParser(builtins.TIMETZ, val => moment(val).format('HH:mm:ss'));
setTypeParser(builtins.TIMESTAMP, val => moment(val).format('YYYY-MM-DD HH:mm:ss'));
setTypeParser(builtins.TIMESTAMPTZ, val => moment(val).format('YYYY-MM-DD HH:mm:ss'));

const db = knex({
    client: 'pg',
    connection: {
        host : 'localhost',
        user : 'MHamzaRajput',
        password : 'My@pass@123',
        database : 'my_db',
    }
})

Upvotes: 0

Lytvoles
Lytvoles

Reputation: 504

Next snippet works like a charm for me:

const { setTypeParser, builtins } = require('pg').types;

const typesToReset = [
  builtins.DATE,
  builtins.TIME,
  builtins.TIMETZ,
  builtins.TIMESTAMP,
  builtins.TIMESTAMPTZ,
];

function resetPgDateParsers() {
  for (const pgType of typesToReset) {
    setTypeParser(pgType, val => String(val)); // like noParse() function underhood pg lib
  }
}

...and call this function before using your pg or library/orm depends on pg (knex, objection etc.) requests.

sources: https://github.com/Vincit/objection.js/issues/663#issuecomment-351678734 and console.log(pg.types)

Upvotes: 6

René Winkler
René Winkler

Reputation: 7088

oracleDb Users have to set this environment variable

export ORA_SDTZ='UTC'

Further info: https://community.oracle.com/docs/DOC-1008911

Upvotes: 1

Mikael Lepistö
Mikael Lepistö

Reputation: 19728

Selecting the format in which dates etc. column types are returned in javascript is done by database driver and not in knex.

For postgresql

Can we always fetch date column as string (varchar) with knex and postgres?

and for mysql

Knexjs returning mysql timestamp, datetime columns as Javascript Date object

should help.

Upvotes: 2

COLBY BROOKS
COLBY BROOKS

Reputation: 329

var data = {
    "transactionDate": "2018-11-29T00:00:00.000Z",
    "transactionTime": "1970-01-01T2:22:00.000Z"
}

var date = data.transactionDate.split("T")[0];
var time = data.transactionTime.split("T")[1].split(".000Z")[0];
let h = parseInt(time.split(":")[0]);
let m = time.split(":")[1];
let s = time.split(":")[2];
let suffix = "AM";
if(h >= 12){
	h = h==12 ? 12: h%12;
  suffix = "PM";
}
time =("0" + h).slice(-2)+":"+m+":"+s+"/"+suffix;
console.log(date);
console.log(time);

Upvotes: -4

Related Questions