Question 355596 in Javascript Question posted in
A very good W3school tutorial can be found here.

return correct format for mysql DateTime field in javascript

I am trying to populate a DateTime field in a mysql table with a value coming from a js script.

So far I have:

let expiredAt = new Date();
expiredAt.setSeconds(expiredAt + process.env.JWT_REFRESH_EXPIRATION);

but it returns

‘Thu Mar 28 2024 04:31:40 GMT+0100 (Ora standard dell’Europa centrale)

that is throwing error in mysql since it is not the correct format. I should be inserting:

2024-03-28 04:31:40

How can I return it? I know there are several similar questions that are focusing on formatting the date on a specific locale. What I want is to convert it to something that is not a locale

Tags:

2

Answers


  1. 0

    You need to fromat the date to return the right format you want, here is an helper function that can do it

    function formatDate(dateString) {
    // Parse the input date string
    var date = new Date(dateString);

    // Extract date components
    var year = date.getFullYear();
    var month = (date.getMonth() + 1).toString().padStart(2, '0'); // Months are zero-based
    var day = date.getDate().toString().padStart(2, '0');
    var hours = date.getHours().toString().padStart(2, '0');
    var minutes = date.getMinutes().toString().padStart(2, '0');
    var seconds = date.getSeconds().toString().padStart(2, '0');

    // Construct the formatted date string
    var formattedDate = `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;

    return formattedDate;
}

    here is how to use it:

    let expiredAt = new Date();
expiredAt.setSeconds(expiredAt + process.env.JWT_REFRESH_EXPIRATION);
const formatedDate = formatDate(expiredAt)

    now you can store the formatedDate to the sql db.

    EDIT:

    in order to avoid different locale , and "escape" the locale. you can set the date to be always utc. and use setUTC methods to format the date in a way that it will be always utc. this way you will "avoid" locale, by insuring you always store utc format.

    For example you can generate the date with new Date(Date.UTC(...)) and/or setSeconds with setUTCSeconds.

    also i see you are using it to set seconds to add the expire seconds to the date, im not sure what is the exact use case, but you can think about manipulating the date on the backend, so you will use your server locale, therefore, all the dates will use the same base date, even if converting to utc (or not) . you can check the expire on the backend and be sure its always the same locale..

    But basicaly to answer your question about locale, there is no way to avoid locale on js, atleast from what i know. time stamp will be always based on a locale

    By the way, this is what mongoDB is doing. i know we are talking about SQL. but just as a reference, mongoDB is converting all dates to be UTC.

    Login or Signup to reply.
  2. 0

    You need to convert the Date instance into a string and then format it accordingly.

    One approach is to use JavaScript’s built-in method toISOString(). In this example you also need to use slice() and replace() to remove the ‘T’ character from the string (T indicates where the time part of the string begins).

    let expiredAt = new Date();

expiredAt.setSeconds(expiredAt + process.env.JWT_REFRESH_EXPIRATION);

console.log(expiredAt.toISOString().slice(0, 19).replace('T', ' '));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search