How do I get timestamp of exact time in sql? | discord.js - imagine an app | Page 1

last oyster Dec 4, 2023, 4:30 PM

#

 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 minnowBOT Dec 4, 2023, 4:30 PM

#

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 Dec 4, 2023, 4:31 PM

#

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

warped zinc Dec 4, 2023, 4:32 PM

#

by using TIMESTAMP or DATETIME as datatype. not just DATE

last oyster Dec 4, 2023, 4:32 PM

#

Ohh

#

ok

#

I'm getting timestamp not of my timezone

#

idk why

#

It's showing 6 hours before

#

my timezone

warped zinc Dec 4, 2023, 4:37 PM

#

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

last oyster Dec 4, 2023, 4:37 PM

#

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

Ahh, how do I fix it then?

warped zinc Dec 4, 2023, 4:38 PM

#

last oyster my timezone

what timezone is that? UTC+-x

last oyster Dec 4, 2023, 4:38 PM

#

warped zinc what timezone is that? UTC+-x

#

+5:30

warped zinc Dec 4, 2023, 4:38 PM

#

that db entry is in UTC

last oyster Dec 4, 2023, 4:39 PM

#

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

#

any idea/

warped zinc Dec 4, 2023, 4:39 PM

#

by displaying it as a timestamp!

last oyster Dec 4, 2023, 4:39 PM

#

yeah

warped zinc Dec 4, 2023, 4:39 PM

#

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

last oyster Dec 4, 2023, 4:40 PM

#

just show 5 hours 30 minutes ahead in timestamp?

last oyster Dec 4, 2023, 4:40 PM

#

warped zinc internally time should always be stored in UTC, so that's totally correct and va...

oh

last oyster Dec 4, 2023, 4:40 PM

#

last oyster just show 5 hours 30 minutes ahead in timestamp?

Is that a good solution for it?

knotty zodiac Dec 4, 2023, 4:40 PM

#

No

#

How do you intend to display the time

#

On discord?

last oyster Dec 4, 2023, 4:40 PM

#

yeah

violet oxideBOT Dec 4, 2023, 4:40 PM

#

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

knotty zodiac Dec 4, 2023, 4:41 PM

#

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

last oyster Dec 4, 2023, 4:41 PM

#

Ok, ty!

knotty zodiac Dec 4, 2023, 4:41 PM

#

Or optionally just <t:seconds:flag>

last oyster Dec 4, 2023, 4:41 PM

#

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

knotty zodiac Dec 4, 2023, 4:42 PM

#

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

warped zinc Dec 4, 2023, 4:42 PM

#

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

last oyster Dec 4, 2023, 4:42 PM

#

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 Dec 4, 2023, 5:06 PM

#

You dont need to convert it

#

Discord does that

last oyster Dec 4, 2023, 5:07 PM

#

so what do i do?

#

convert this timezone into milliseconds?

knotty zodiac Dec 4, 2023, 5:07 PM

#

Seconds

last oyster Dec 4, 2023, 5:08 PM

#

knotty zodiac Or optionally just <t:seconds:flag>

and then use this?

knotty zodiac Dec 4, 2023, 5:08 PM

#

Yes

last oyster Dec 4, 2023, 5:11 PM

#

knotty zodiac Yes

uhh

knotty zodiac Dec 4, 2023, 5:11 PM

#

...

knotty zodiac Dec 4, 2023, 5:12 PM

#

violet oxide Message formatting (mentions, timestamps, emoji, etc.): [learn more](https://dis...

Replace flag with the actual flag

last oyster Dec 4, 2023, 5:13 PM

#

knotty zodiac Replace flag with the actual flag

In this case, should I use this?

#

or this one

knotty zodiac Dec 4, 2023, 5:13 PM

#

One is styled, one isnt

#

Try it out, choose for yourself

last oyster Dec 4, 2023, 5:13 PM

#

Alright

warped zinc Dec 4, 2023, 5:14 PM

#

and scroll down a bit further for examples of all styles

last oyster Dec 4, 2023, 5:14 PM

#

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 Dec 4, 2023, 5:16 PM

#

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

last oyster Dec 4, 2023, 5:16 PM

#

not really so

warped zinc Dec 4, 2023, 5:17 PM

#

because the time you stored in the DB is not now

last oyster Dec 4, 2023, 5:17 PM

#

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 Dec 4, 2023, 5:19 PM

#

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 Dec 4, 2023, 5:21 PM

#

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

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 Dec 4, 2023, 5:21 PM

#

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

last oyster Dec 4, 2023, 5:21 PM

#

MySQL

warped zinc Dec 4, 2023, 5:22 PM

#

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

last oyster Dec 4, 2023, 5:23 PM

#

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

So this timezone is of the hosting database?

warped zinc Dec 4, 2023, 5:24 PM

#

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

👆 yes

last oyster Dec 4, 2023, 5:25 PM

#

warped zinc 👆 yes

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

warped zinc Dec 4, 2023, 5:28 PM

#

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

last oyster Dec 4, 2023, 5:30 PM

#

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

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

warped zinc Dec 4, 2023, 5:33 PM

#

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

last oyster Dec 4, 2023, 5:33 PM

#

warped zinc that page has the syntax explained as well as examples. did you even bother to r...

Yeah ofc I did

#

I think it's related here

#

But this part I am confused

warped zinc Dec 4, 2023, 5:34 PM

#

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

last oyster Dec 4, 2023, 5:34 PM

#

last oyster But this part I am confused

I am confused in this part

warped zinc Dec 4, 2023, 5:35 PM

#

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

last oyster Dec 4, 2023, 5:38 PM

#

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 Dec 4, 2023, 5:40 PM

#

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

#How do I get timestamp of exact time in sql?