Node-PostgreSQL time format-CodePudding

I have two tables named in users and card_info in my Postgres database. I have created an endpoint for registering new users and I have given a field called dateCreated as shown in the code below.

 const handleRegister=(req,res,db,bcrypt,saltRounds)=>{
    const {name,phno,dob,email,username,password,aadharCard}=req.body;
    if(!name || !email|| !phno || !dob || !username || !password || !aadharCard){
        return res.status(400).json('Wrong Form Submission');
    }
    
    const hash=bcrypt.hashSync(password,saltRounds);
    const accNo=Math.floor(2E9 Math.random()*1E9);
    const dateCreated=new Date().toJSON().slice(0,10);

    db('users')
        .insert({
            username:username,
            name:name,
            email:email,
            password:hash,
            dob:dob,
            phno:phno,
            aadhar:aadharCard,
            acc_no:accNo,
            created_date:dateCreated
        })
        .returning('*')
        .then(users=>{
            res.json(users[0]);
        })
        .catch(err=>res.status(400).json("Unable to register"));
    }
    module.exports={
        handleRegister:handleRegister
    }

In the card endpoint, I am selecting the user's created year whose id is sent as a request. I then store these values into the card_info table using calculated values. The expiration date is supposed to be 4 years more than the created_year. The id of the table users is a foreign key of the table card_info

Card.js

const genCard=(req,res,db)=>{
    const {id,cardType} =req.body;
    
    db.select('created_date').from('users').where("id","=",id)
        .then(data=>{
            const createdDate=data[0].created_date.toString();
            db('card_info')
            .insert({
                card_number:Math.floor(Math.random()*1E17),
                owner_id:id,
                expiration_date: createdDate.replace(parseInt(createdDate),parseInt(createdDate) 4),
                cvv:Math.floor(Math.random()*1E3),
                card_type:cardType
            })
            .returning('*')
            .then(users=>{
                console.log(res.json(users[0]));
            })
            .catch(err=>res.json(err.message))
        })
        .catch(err=>res.json(err));
}


module.exports={
    genCard
}

This is the error that I recieve: "insert into "card_info" ("card_number", "card_type", "cvv", "expiration_date", "owner_id") values ($1, $2, $3, $4, $5) returning * - time zone "gmt 0300" not recognized".

I have tried various date formatting methods but nothing seems to work. Please let me know if someone has a solution to this.

CodePudding user response：

I would avoid all the attempts to manipuate strings that represent dates–it only results in tears and often doesn't consider edge cases. Rather manipulate the date directly. I would recommend Luxon that provides a battled-tested wrapper around Date and has convenience functions that make dealing with dates easier.

    // convert to DateTime object
    const createdDate = DateTime.fromJSDate(data[0].created_date);

    // add 3 months
    const futureDate = createdDate.plus({months: 3});

    // insert a date object
    db('card_info').insert({
       card_number:Math.floor(Math.random()*1E17),
       ...
       created: futureDate, // or futureDate.toJSDate()
    })

Demo:

const {
  DateTime
} = luxon;

// convert a saved date from the DB to DateTime
const dt = DateTime.fromJSDate(new Date());

// add 3 months
const futureDate = dt.plus({
  months: 3
});

console.info(futureDate.toFormat('yyyy-MM-dd'), futureDate.toJSDate());

<script src="https://cdn.jsdelivr.net/npm/[email protected]/build/global/luxon.min.js"></script>