"

"

|
blog 2 image

Parse SQL timestamp

3 min read

Prequisite:
JavascriptSQLRegular Expression
  • Should have good knowledge of Javascript
  • Basic Regular Expression and SQL know how, but not required if you only want to copy code snippet

       If you regularly collect api data from the server, you might sometimes notice something peculiar when gathering the date data. The date value could appear as 2021-06-01T11:08:01.000Z. The  T and  Z are something you normally do not want appearing if you require the date value to be displayed within the web UI. Plus, the milli seconds does not look great to the average person unless it's for something very specific. The ideal output date and time would be  2021-06-01 11:08:01.

In order to get the desirable date output, we need to do a bit of conversion. One way is to use a regular expression to replace T with an empty space and then cut off everything after seconds. Funny enough, I am going to show you how.

Solution:
// eg. sqlTimestamp: 2021-06-01T11:08:01.000Z
function(sqlTimestamp) {
const removeT = sqlTimestamp.replace("T", " ");

const removeMilliSecondRegEx = /\.\w+/g;

const timestamp = removeT.replace(removeMilliSecondRegEx, ""); 

return timestamp
}
// Output: "2021-06-01 11:08:01"

That's about it. A short function which could save you some hassle when parsing SQL date format.