Hi there,
I am very fairly new to MySQL and wondered the best way to create a age column and also a date field that records the date that the user submitted a form into the database.
Can i just use something like
age INT(3)
Any help would be great, thanks.
the number in brackets in a numeric specification like INT(3) does not mean what you think it means
an INT or INTEGER column can hold ~any~ integer value between -2,147,483,648 and 2,147,483,647
perhaps you might want MySQLās TINYINT, which allows values between -128 and 127
SMALLINT is standard SQL and can hold values between -32768 and 32767
the number in parentheses refers to how many digits you want displayed when using the ZEROFILL option, which almost nobody uses
that said, storing birthdate is a much better strategy than storing age, because age is one of those data elements that, ahem, ages badly
as for āa date field that records the date that the user submitted a form into the databaseā you should probbaly use a DATE column
There is no sence to create column āageā. Personās age changed itself permanently. You should to save personās birthday. Age is calculatable attribute of query, if you need it.
Thanks for the replies.
I am getting a bit lost now.
This is an email field that I have from one of my other fields:
<input name="email" ng-model="home.quizDatabase.formEmail" type="email" required
ng-focus="formData.$submitted=false">
If I were to create a date field, something like this:
<input type="date" name="birthday" required ng-model="home.quizDatabase.formBirthday" ng-focus="formData.$submitted=false" min="1900-01-01" max="2100-12-31">
I assume it would input the date into my table in the birthday column something like 1976-01-12
How would I then create a column that would calculate the age automatically and also update automatically when they have a birthday?
Yes, the date type input will give the date in YYYY-MM-DD format, which should be good for the database.
You donāt need a column for age, but you can calculate it on demand using the date of birth.
Assuming you are using PHP, you can set up a little function to calculate age from DOB.
you would not create an age column ā instead, you would calculate a personās age at the time you select the data for a person
you could use an approximate formula ā
SELECT person_id
, FLOOR(DATEDIFF(CURRENT_DATE,birthday)/365.25) AS age
FROM ...or else you could develop a more robust, exact calculation based on February 29 and all that stuff
Thanks.
Yes I am using PHP.
I see, so I would create the age outside of the database. I guess I would also rearrange the date so it would be a UK or US date format?
Iāve also just realised the date input would need users to scroll back a long way if they are older:
If I were to have a select for year, month and day it wouldnāt be a date input so it wouldnāt input the date into a single column in the database?
You could do it either way, depending on what best suits your needs.
I think the date pickers vary a bit between browsers. You can do it by typing, but I think some date pickers are maybe more intuitive than others.
I see. If I wanted to see their age, I would calculate it when I select the data as per @r937ās comment? If so, then there is not a way to see it in the database when I log into phpmyadmin where I can see the other data such as the date?
Yes, that is probably the best way to retrieve an age from the database.
If you view the table in PHPmyadmin, you would see dates in the table, not the DOB. The age needs to be calculated from the difference of the DOB to the present date. The table shows the actual data you store, your app/page/whatever shows data as you wish it to be represented.
I see, thanks, that makes sense now 
Sorry Iām fairly new to database stuff so getting my head around it.
Effectively storing data can be a different way of thinking. Age is a good example of that.
I you want to know someoneās age, you may directly ask their age, not ask their DOB and work it out.
But in data, a DOB is a constant, it does not change.
Whereas age is a relative value and changes all the time.
To store the actual age, you would have to run a script every day to see if itās anyoneās birthday, then update any age it finds. Easier to store a constant value that never changes.
ā¦And it occurs that you would need the DOB to do that anyway.
Thank you, that is an awesome way of explaining it and easier to understand
and this of course implies the more complex February 29 stuff
whereas dividing by 365.25 is less accurate but simpler
Iām struggling to find a date field that will work in all browsers.
Would it work if I were to use a text field with DD/MM/YYYY as a placeholder? If this is then entered to the database, would I still be able to calculate birthdays?
Browsers that donāt support date inputs, revert to text anyway, so you could still use date.
Though date pickers may display like DD/MM/YYY, what results in post is YYYY-MM-DD, which is good for the database, though maybe not a familiar format for non-dev users. But it may be a better hint format for the fall-back in non-supporting browsers.
Another option is individual day, month, year fields. Again, these are new input types.
Iām sure date pickers has been discussed before, but I donāt recall the outcome.
It may be worth a topic spilt.