How can I declare time and date variables in mysql ?

Question

0.00/5 (No votes)

See more:

How can i declare time variables of the form XX:YY and date variables of the form
dd-mm-yyyy in MySQL?

What I have tried:

I'm working on a project about a cinema database and I want to create date and time variables for the start of a movie broadcast

Posted 7-Aug-19 4:41am

Member 14549747

Updated 7-Aug-19 6:22am

Add a Solution

Comments

Richard MacCutchan 7-Aug-19 12:06pm

Do not store dates and times as text strings. Use the proper data types as described in the documentation.

2 solutions

Solution 2

Actually you cannot; as Date and Time types in most RDBMS systems are actually numbers.
It is up to whatever program you are using to view the information to actually format it into a human-recognizable format at the presentation level.

The way to do this is to present this is to use the Date_Format() which will convert the Date/Time into as string.

Reference:
[1]: MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions[^]

Posted 7-Aug-19 6:22am

MadMyche

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

k5054 · Accepted Answer · 2019-08-07T05:19:00

Solution 1

GIYF: MySQL :: MySQL 8.0 Reference Manual :: 11.3 Date and Time Types[^]
If you insist on "dd-mm-yyyy' format, which may be ambiguous if your users expect "mm-dd-yyyy', you may have to google some more to see how to format dates appropriately. I'd suggest you stick with "yyyy-mm-dd", its unambiguous, and as a bonus, if you need (or want) to sort by date in the app, as opposed to retrieving sorted data from the database, a straight string compare works correctly, without having to write a method to decompose and compare the dates.

Posted 7-Aug-19 5:19am

k5054

Comments

Member 14549747 7-Aug-19 11:22am

Yes "yyyy-mm-dd" . When you say string compare what do you mean ?

k5054 7-Aug-19 12:41pm

If we have a function compare(string1, string2) then compare("2018-08-01", "2019-07-02") will show the first string is less than the second. That's probably what we want. If we use "mm-dd-yyyy",then compare("08-01-2018", "07-02-2019") shows the first string is greater than the second, which is probably not what we want. If we wanted to compare dates using "mm-dd-yyyy", we'd have to write a compare_date(date1, date2) function that extracts, year, month, day from each string and then compares the years, months, and days to determine if date1 is less than date2.

Member 14549747 7-Aug-19 14:58pm

Thank you very much .