3

I'm trying to automate some scripts that i've been running weekly.

The mySQL script:

select count(*), date(updated) from myDB.table where itemId = 50 and date(updated) >= "2017-12-01" and date(updated) <= date(now()) group by date(updated)

In knexjs file: 

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host: myIP,
    port: myPort,
    debug: true,
    dateStrings: true,
    user: user,
    password: pwd,
    database: myDB
  }
})

The query building part: 

(async () => {
  let data = await knex
 .select('updated')
 .count()
 .from('myDB.table')
 .where('itemId', '=', 50)
 .where('updated', '>=', '2017-12-01')
 .where('updated', '<=', Date())
 .groupByRaw('date(updated)')

  console.log(data)
})()

How do i add date formating within .where() and .select() clause, in knexjs syntax?

lucidkodo
  • 31
  • 3
  • So is your question "How do I format `Date()` as YYY-MM-DD" (noting that `Date()` is equivalent to `new Date().toString()`)? So probably a duplicate of [*Where can I find documentation on formatting a date in JavaScript?*](https://stackoverflow.com/questions/1056728/where-can-i-find-documentation-on-formatting-a-date-in-javascript?s=1|1212.2047) – RobG Dec 22 '17 at 00:07
  • 1
    hi @RobG, the `Date()` you meant is the JS date prototype, but what i'm looking for is the `date()` in _sql queries_, that "parses" fields into proper dates (because datetime field in sql mostly in 'YYYY-MM-DD HH:mm:ss' format? I'm looking for a way to "parse" the datetime field data to "YYYY-MM-DD' format, using the knex engine, written in JS.. – lucidkodo Dec 22 '17 at 07:08

0 Answers0