How do I get timestamp of exact time in sql?

[last oyster]

 sql = `CREATE TABLE IF NOT EXISTS Modlogs (Guild_id VARCHAR(255), User_id VARCHAR(255), Moderator_id VARCHAR(255), Punishment_type VARCHAR(255), Reason VARCHAR(255), Timestamp DATE DEFAULT NOW())`
  con.query(sql, function (err, result) {
    if (err) throw err;
   
  });```

Here is my sql query

It's showing timestamp in date but not exact time, how I do that?

[elfin minnow]

• Consider reading #how-to-get-help to improve your question!
• Explain what exactly your issue is.
• Post the full error stack trace, not just the top part!
• Show your code!
• Issue solved? Press the button!
• ✅ Marked as resolved by OP

[last oyster]

I have tried using all functions to get current time in sql but it's only showing current date

[warped zinc]

by using TIMESTAMP or DATETIME as datatype. not just DATE

[last oyster]

Ohh

ok

I'm getting timestamp not of my timezone

idk why

It's showing 6 hours before

my timezone

[warped zinc]

either your DBMS or your node process are using a different timezone then

[last oyster]

Ahh, how do I fix it then?

[warped zinc]

what timezone is that? UTC+-x

[last oyster]

+5:30

[warped zinc]

that db entry is in UTC

[last oyster]

o, so how do i make it in my timezone?

any idea/

[warped zinc]

by displaying it as a timestamp!

[last oyster]

yeah

[warped zinc]

internally time should always be stored in UTC, so that's totally correct and valid

[last oyster]

just show 5 hours 30 minutes ahead in timestamp?

[last oyster]

oh

[last oyster]

Is that a good solution for it?

[knotty zodiac]

No

How do you intend to display the time

On discord?

[last oyster]

yeah

[violet oxide]

Message formatting (mentions, timestamps, emoji, etc.): learn more

[knotty zodiac]

Use the time function from djs to parse it to a timestamp

[last oyster]

Ok, ty!

[knotty zodiac]

Or optionally just <t:seconds:flag>

[last oyster]

Also it don't happens like this in mongoose, idk why it happens like this in sql

[knotty zodiac]

That automatically converts it to the timezone of the user looking at it

[warped zinc]

it does, you just don't look at it raw like you do in your SQL DBMS

[last oyster]

ah

uhh

I am not able to convert it into timezone

it's not getting converted into my timezone

this is the method I'm using

[knotty zodiac]

You dont need to convert it

Discord does that

[last oyster]

so what do i do?

convert this timezone into milliseconds?

[knotty zodiac]

Seconds

[last oyster]

and then use this?

[knotty zodiac]

Yes

[last oyster]

uhh

[knotty zodiac]

...

[knotty zodiac]

Replace flag with the actual flag

[last oyster]

In this case, should I use this?

or this one

[knotty zodiac]

One is styled, one isnt

Try it out, choose for yourself

[last oyster]

Alright

[warped zinc]

and scroll down a bit further for examples of all styles

[last oyster]

The time is still showing not of my timezone

This is what the timestamp I retrieved from database

which I converted into seconds

Using this method

[warped zinc]

that... looks like it's in your timezone though

[last oyster]

not really so

[warped zinc]

because the time you stored in the DB is not now

[last oyster]

oh yea

i forgot about that

lemme try doing now then

uhh this one i just did

and it's showing some other time

if u don't mind, can you try converting that timestamp in your system?

[warped zinc]

you don't show me the timestamp though...

and how did you save it to your db? what's the actual query you use for insert?

[last oyster]

When creating the table I have made the timezone as default so whenever I insert into, it just sets the timezone automatically

 let sql = `INSERT INTO Modlogs(Guild_id, User_id,Moderator_id,Punishment_type,Reason ) VALUES ('${interaction.guild.id}', '${warnUser.id}', '${interaction.user.id}', 'Warning', '${reason}')`;
    con.query(sql, function (err, result) {
      if (err) throw err;
    });

like this

[warped zinc]

well, what DBMS are you using? MySQL, Postgres, sqlite, ...?

[last oyster]

MySQL

[warped zinc]

https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html#time-zone-variables

[last oyster]

So this timezone is of the hosting database?

[warped zinc]

👆 yes

[last oyster]

So can I try like adding the difference of seconds of their timezone and my timezone to it, so it matches

[warped zinc]

or you could set the timezone on your DBMS to match yours. as shown on the page I linked

[last oyster]

can you tell in short, bcoz its making a less sense to me

[warped zinc]

that page has the syntax explained as well as examples. did you even bother to read it?

[last oyster]

Yeah ofc I did

I think it's related here

But this part I am confused

[warped zinc]

then ask a question about parts you didn't understand instead of asking me to explain it completely

[last oyster]

I am confused in this part

[warped zinc]

that's not a question. that's just stating "I don't understand"

[last oyster]

Honestly saying I am not making any sense here, if you could give me some hint of starting so I could try to guess what the other part is saying. If you don't wanna tell, that's fine thanks.

[warped zinc]

the hint would be to read that page... if nothing on that page makes any sense to you you might want to take a course on MySQL syntax first