0

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.

Bazinga777
  • 5,140
  • 13
  • 53
  • 92

2 Answers2

0

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

Community
  • 1
  • 1
John Ruddell
  • 25,283
  • 6
  • 57
  • 86
  • I need to make it for for a certain time period, will this work for that as well ? – Bazinga777 May 19 '14 at 19:01
  • yes if you put the starting date as the start variable and ending date as the end variable – John Ruddell May 19 '14 at 19:02
  • sorry, my question may not have been correct. I want to query data from a certain time interval eg: 7 to 8 AM everyday and want to get data from a timestamp which will be closest to 8 AM, so can i query something like SELECT MIN(date) FROM table WHERE date BETWEEN 7:0:0 AND 7:59:59 – Bazinga777 May 19 '14 at 19:05
  • @Bazinga777 i just edited with the conversion on the date to compare. try it – John Ruddell May 19 '14 at 19:05
  • 1
    Oh so you don't care what day it happens on? just the hour? – John Ruddell May 19 '14 at 19:06
  • @Bazinga777 ok just edited to check off of time.. try that :) – John Ruddell May 19 '14 at 19:10
  • @Bazinga777 i added an update to my answer as well so you can change the values in the database as well.. that will make the query's much easier :) – John Ruddell May 19 '14 at 19:17
0

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)
John Ruddell
  • 25,283
  • 6
  • 57
  • 86
Tanatos
  • 1,857
  • 1
  • 13
  • 12