I have a database dumped from csv where i have a column name time_stamp that I imported using VARCHAR
I have two things that I am confused about now,
1) I want to be able to convert this column to either timestamp or datetime its current format is in dd/mm/yyyy H:M:S but i read that unix accepts only - instead of /. Is this true?
2) I want to query from the database a timestamp value that is the highest within a certain time range using the column above
Example: between 7 AM and 8 AM , it should give me the timestamp which will be the closest to 8AM
Forgive me for my question if my question is vague, but i haven't been able to find much related to my question. I am sure that this is gonna receive some down votes so please be kind.
few things.. first look here to see more about the date format. Insert current date in datetime format mySQL
second you should probably update the table to change the / to a -.
use STR_TO_DATE() to convert
to get the most recent date then just do this
SELECT MIN(date) FROM table -- // MIN() is used to get the most recent timestamp
WHERE STR_TO_DATE(date, '%c/%e/%Y %r') BETWEEN $start AND $end
start and end would be your starting and ending dates
IF YOU ONLY WANT TO CHECK OFF OF THE TIME OF THE DAY USE THIS:
SELECT MIN(date) FROM table -- // MIN() is used to get the most recent timestamp
WHERE TIME(STR_TO_DATE(date, '%c/%e/%Y %r')) BETWEEN $start AND $end
per my previous recommendation you should update the table like this.
UPDATE table
SET date = STR_TO_DATE(date, '%c/%e/%Y %r')
something like that to fix it ... that will make your querys less complicated :)
1) Yes, that is true. Date needs to be YYYY-mm-dd H:M:S. (you can convert it in php and then do an insert (date("Y-m-d H:i:s",strtotime($your_date))))
2)
SELECT * FROM table
WHERE HOUR(date) BETWEEN 'start_date' AND 'end_date'
GROUP BY DAY(date)
ORDER BY DAY(date)