将列转换为时间戳或日期时间，并在一个时间间隔内查询最新值

Question

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.

Answer 1

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 :)