4

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?

user2473015
  • 1,392
  • 3
  • 22
  • 47

5 Answers5

4

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)

Lytvoles
  • 488
  • 4
  • 6
2

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.

Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
  • Thank you I will have a look now, I'm using mssql driver. – user2473015 Dec 06 '18 at 02:12
  • Nops. In my MySQL table I got YYYY-MM-DD but Knex convert it to YYYY-MM-DD HH:mm:ss – Renan Coelho Jul 17 '20 at 19:28
  • Renan, try to read the answer again. It says that the conversion is not made by knex, but by underlying db driver decides how sql types are converted to javascript. For date colums you need to modify a bit the method provided by the linked answer to work for date columns. – Mikael Lepistö Jul 18 '20 at 12:01
  • Do y'all know how to achieve this in MSSQL? It looks like my question is similar: https://stackoverflow.com/q/63838192/3772763 – NamedArray Sep 10 '20 at 22:03
  • Something like `sql.map.register(String, sql.DateTime)` might work. It was mentioned here https://tediousjs.github.io/node-mssql/#input-name-type-value but sounds like it might work to input and output. – Mikael Lepistö Sep 11 '20 at 07:39
1

oracleDb Users have to set this environment variable

export ORA_SDTZ='UTC'

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

René Winkler
  • 6,508
  • 7
  • 42
  • 69
0

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',
    }
})
M. Hamza Rajput
  • 7,810
  • 2
  • 41
  • 36
-3

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);
COLBY BROOKS
  • 331
  • 1
  • 5