Create timestamp in SQL format

      Sometimes the client side has to produce the date of the user interaction before sending it to the server api. And sometimes they have to format the date and time before sending it to the server api. And sometimes they have to specifically format the date and time in SQL date field before sending it to the server api. That's what we will be looking at.

What is a timestamp?

A timestamp is to store the current date and time for future references

What is SQL format?

SQL stands for Structured Query Language. It lets you access and manipulate databases. But there is one database field we are particularly interested in, the created_on field. The created_on field is where dates and time are stored within the database (there are other fields that can store date and times too).

In order to store timestamp within created_on, you will first need to transform it to the required structure. Depending on the SQL server, you might have to format the timestamp yourself before sending to the server if the server is not setup for that. The most common SQL format is yyyy-mm-dd hh:mm:ss. But that can be a little tricky do.

Solution:

function  createTimestampInSqlFormat() {
const date = new Date();
let year =  date.getFullYear();
let month =  date.getMonth() + 1
let day =  date.getDate();
let hour =  date.getHours();
let minute =  date.getMinutes();
let second =  date.getSeconds();

const timestampArray = [year, month, day, hour, minute, seconds];

let timestampString = ""

for (let dateNumber of  timestampArray) {
// if number is less than 10 than add a 0 in front of it
if (dateNumber >= 10) {
timestampString += dateNumber;
} else {
timestamString += `0${dateNumber}`;
}
}

const/ timestampRegEx = /(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})/g;

// create the sql timestamp format.You could change the format here if need be 
const/ timestamp = timestampString.replace(
timestampRegEx,
"$1-$2-$3 $4:$5:$6"
);

return timestamp;
}

Basically we collect the date as usual but setting it up in SQL format will require some help from regular expression.

So whenever you need to create a timestamp in SQL format, you can use this tried and test function before sending it off to the server api.