MySQL timestamp field return empty object in API response

Question

i've a simple table in MySQL with 2 timestamp fields:

*"...

creation timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_edit timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

..."*

When i try to fetch with a simple query:

SELECT * FROM customers

from my Node.js (mysqljs), json return:

creation: {}, // empty object

last_edit: {} // empty object

how can i get the date???

Thank in advance! :)

Answer 1

You need to insert at least one record into your table for any results to come back from a query against it.

If you for some reason just want to retrieve the current date from the database, you can do:

SELECT CURRENT_TIMESTAMP

or

SELECT NOW()

Answer 2

I am guessing that this is a JDBC issue as I have seen similar behaviour.

In my case, the column was defined as

`last_edit` datetime DEFAULT NULL

The JDBC getTimestamp() method (as suggested as the correct getter) was returning {}.

I found that using the getObject() method returned the expected DATETIME value in ISO string format (good enough for my use cases).

Just noticed that you say that you are using "mysqljs" so not sure if this fix is relevant to your situation as the JDBC part is abstracted away...

UPDATED: For some reason getObject() returned a time that was +7 hours from the time displayed in the DB (UTC).

Examining the output of getTimestamp() showed that it was an object with getters and setters (not present after 'stringify-cation') and wrote a quick converter.

function convert_ts_(ts) {
    var date = (ts.getYear() + 1900) + '-' + ((ts.getMonth() + 1) > 9 ? ts.getMonth() + 1 : '0' + (ts.getMonth() + 1)) + '-' + (ts.getDate() > 9 ? ts.getDate() : '0' + ts.getDate());
    var time = (ts.getHours() > 9 ? ts.getHours() : '0' + ts.getHours()) + ':' + (ts.getMinutes() > 9 ? ts.getMinutes() : '0') + ":" + (ts.getSeconds() > 9 ? ts.getSeconds() : '0' + ts.getSeconds());
    return date + 'T' + time;
}

This returns the date in "YYYY-MM-DDTHH:MM:SS" format with zero padding