Andurit Andurit - 2 years ago 123
AngularJS Question

Best way to store dates in MySQL database for data visualize

I make some website which will be similar to basic TODO List applications. For a single row I always store single date and I basicly dont need hours and seconds, date is just fine.

How I use data:

I have PHP backend which make SQL select and then create JSON from this data and give it frontend which is created in AngularJS.

What I want to do with data:

I want to compare data and visualize it with graphs. I am not sure whats the best way to store this kind of values.

Things I consider:

  1. Unix TIME - I feel like its really easy to work with numbers in any language and also easy to compare and visualize

  2. MySQL Date - I'm not sure because I think that inside MySQL date format should be really usefull but I'm not sure how to compare date in format like: YYYY-MM-DD

  3. MySQL Datetime - Basicly same as before just with added time, I dont think this can be usefull for me.

I will be really happy your opinion and advise which can help me. Also advantage of each method because I mentioned just advantage of UNIX TIME because number are easy to compare. It's also easy I miss something relevant how to store date in database so if there is other option please don't hesitate to speak about it.


Answer Source

I would opt for MySQL Date.

Lets say we have a table called my_table which has a Date column named my_date.

You can actually interact with it in much the same way as you can a timestamp. Assuming my_date is the date that the item was created...

// Where it was created after 1st Jan
SELECT * FROM my_table WHERE my_date > '2016-01-01'

// Where it was created in Jan
SELECT * FROM my_table WHERE my_date BETWEEN '2016-01-01' AND 2015-01-31

// Where it was created in the last 7 days
SELECT * FROM my_table WHERE DATEDIFF(CURDATE(), my_date) <= 7
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download