-1

I want minimum Data From MySql DataBase. In my database Column's DataType is VARCHAR and i inserting data is like as "2011-03-01 09:00" (yyyy-MM-dd hh-mm). Now i need Minimum Date from my Database. How I can do this.? Please Help Me. Thanks in advance.

ajreal
  • 46,720
  • 11
  • 89
  • 119
Niranj Patel
  • 32,980
  • 10
  • 97
  • 133
  • What do you mean by minimum date?. "0000-00-00 00:00" is not good enough? – Zimbabao Feb 25 '11 at 12:08
  • 3
    start again and change the datatype to DATE... – TimCodes.NET Feb 25 '11 at 12:09
  • What you you mean by minimum date? – Pekka Feb 25 '11 at 12:11
  • I dont want use DATETIME data-type. – Niranj Patel Feb 25 '11 at 12:11
  • 1
    @Pekka: minimum date means nearest date from current date. – Niranj Patel Feb 25 '11 at 12:12
  • 2
    @CapDroid but DATETIME is exactly the right type for this. What speaks against using it? Also can you make an example of what you mean by nearest date? – Pekka Feb 25 '11 at 12:13
  • i need to set alarm for upcoming date in database. – Niranj Patel Feb 25 '11 at 12:15
  • So what you're after is the next row (in date order) where the date/time is greater than the current date/time? See my answer. – Adam Hopkinson Feb 25 '11 at 12:19
  • Change the VARCHAR to DATETIME and you're done. The format yyyy-MM-dd hh-mm is correct, you don't have to change anything. You can now use MIN(), MAX(), ORDER BY, etc. it all works correct on a DATETIME. – Frank Heikens Feb 25 '11 at 12:25
  • 1
    @Cap: By not using a datetime field, you've painted yourself into this corner. The datetime field is EXACTLY what you need, then you can simply do `select min(yourfield)`. By storing as strings, you force Mysql to do a costly type-conversion from string->date for EVERY FIELD, on EVERY QUERY. – Marc B Feb 25 '11 at 16:28

2 Answers2

0

Convert the col into a unix timestamp, get the current date as a unix timestamp, get the difference, make it a positive number, order by that number, return the first row.

SELECT * FROM tablename ORDER BY ABS(UNIX_TIMESTAMP(dateCol) - UNIX_TIMESTAMP(NOW())) ASC LIMIT 1

Edit: from your comments, you need the next row (ordered by date):

SELECT * FROM tablename WHERE UNIX_TIMESTAMP(dateCol) > UNIX_TIMESTAMP(NOW()) ORDER BY UNIX_TIMESTAMP(dateCol) ASC LIMIT 1
Adam Hopkinson
  • 28,281
  • 7
  • 65
  • 99
0

sort the table by your date (string) column ascending. limit the rows to the first. get you date column. then you have the smallest date.

no conversions are needed.

this is only possible, when you are using this format: yyyy-MM-dd hh-mm but you are doing this, already

select datecol from table order by 1 limit 1,1

maybe the limit in the sample is wrong - i don't know if mysql starts at 1 or 0

coding Bott
  • 4,287
  • 1
  • 27
  • 44