How to convert MySQL DATETIME value to JSON format in JavaScript?

To convert MySQL DATETIME values to JSON format in JavaScript, you can parse the datetime string into a Date object and then use JSON.stringify() to convert it to JSON. This is useful when working with MySQL data in web applications.

Understanding MySQL DATETIME Format

MySQL DATETIME format is typically YYYY-MM-DD HH:MM:SS. JavaScript's Date constructor can parse various date formats, making conversion straightforward.

Method 1: Converting to JSON Object with Individual Components

This approach extracts individual date components and creates a structured JSON object:

<script>
// Simulate MySQL DATETIME string
var mySQLDateTime = new Date("2019-09-06 22:54:48");
var yearValue = mySQLDateTime.getFullYear();
var dateValue = mySQLDateTime.getDate();
var monthValue = mySQLDateTime.getMonth() + 1; // Month is 0-indexed
var hour = mySQLDateTime.getHours();
var minutes = mySQLDateTime.getMinutes();
var second = mySQLDateTime.getSeconds();

var jsonObject = {
    "year": yearValue,
    "month": monthValue,
    "DateValue": dateValue,
    "Hour": hour,
    "Minutes": minutes,
    "Second": second
};

var dateJsonObject = JSON.stringify(jsonObject);
document.write(dateJsonObject);
</script>

{"year":2019,"month":9,"DateValue":6,"Hour":22,"Minutes":54,"Second":48}

Method 2: Direct Date Object to JSON

You can also convert the entire Date object directly to JSON, which uses ISO 8601 format:

<script>
var mySQLDateTime = new Date("2019-09-06 22:54:48");
var dateJsonString = JSON.stringify(mySQLDateTime);
document.write("Direct conversion: " + dateJsonString);
</script>

Direct conversion: "2019-09-06T22:54:48.000Z"

Method 3: Custom Formatting Function

For more control over the JSON structure, create a custom formatting function:

<script>
function mysqlDateTimeToJSON(mysqlDateTime) {
    var date = new Date(mysqlDateTime);
    return JSON.stringify({
        timestamp: date.getTime(),
        formatted: date.toISOString(),
        components: {
            year: date.getFullYear(),
            month: date.getMonth() + 1,
            day: date.getDate(),
            hour: date.getHours(),
            minute: date.getMinutes(),
            second: date.getSeconds()
        }
    });
}

var result = mysqlDateTimeToJSON("2019-09-06 22:54:48");
document.write(result);
</script>

{"timestamp":1567807488000,"formatted":"2019-09-06T22:54:48.000Z","components":{"year":2019,"month":9,"day":6,"hour":22,"minute":54,"second":48}}

Comparison of Methods

Important Notes

Remember that JavaScript months are 0-indexed (January = 0), so add 1 when converting to match MySQL's 1-indexed months. Also, ensure your MySQL DATETIME strings are in a format that JavaScript's Date constructor can parse.

Conclusion

Converting MySQL DATETIME to JSON in JavaScript is straightforward using JSON.stringify(). Choose the method based on whether you need individual components, ISO format, or a comprehensive structure.