0

I have a MYSQL table which has a column name called timestamp stored in string format. the timestamps in this column are in the following format e.g. '20/10/2014 05:39 PM'

Now how can select a row and convert the timestamp column to 24HR format on the fly.

basically I want something like this. SELECT id, user, STR_TO_DATE(timestamp, '%d/%m/%Y %h:%i %p') as timestamp FROM mytable WHERE user="bob";

but this does not work. looks like its not recognizing the timestamp variable inside STR_TO_DATE sql function and its retuning NULL for the timestamp column.

Please help.

Tom Tom
  • 3,680
  • 5
  • 35
  • 40
Nithy Kumar
  • 13
  • 1
  • 3
  • 2
    *"I have a MYSQL table which has a column name called timestamp stored in string format."* - This is where you went wrong. The right way would be to store it as a date or timestamp, and convert it when you want to format it in a specific format. I think the query goes wrong, because `timestamp` is a reserved word. Try to enclose it in backticks (`STR_TO_DATE(\`timestamp\`, '%....')`), or rename the column altogether. – GolezTrol Feb 23 '15 at 01:05
  • tried both enclosing and then renaming the column name. still returning NULL as value. – Nithy Kumar Feb 23 '15 at 02:11

2 Answers2

0

Looks fine to me:

mysql> SELECT STR_TO_DATE('20/10/2014 05:39 PM', '%d/%m/%Y %h:%i %p');
+---------------------------------------------------------+
| STR_TO_DATE('20/10/2014 05:39 PM', '%d/%m/%Y %h:%i %p') |
+---------------------------------------------------------+
| 2014-10-20 17:39:00                                     |
+---------------------------------------------------------+

(I know this should be a Comment, but is an Answer in order to get formatting.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • It also works for me: http://sqlfiddle.com/#!2/f2ca6/3 Perhaps the original data has null in the column? – Turophile Feb 23 '15 at 02:26
  • yes, this works if you supply the data directly. but i want to select a column and convert that column results on the fly. i.e. `timestamp` column. – Nithy Kumar Feb 23 '15 at 02:35
  • My bad. i had some typo in my query and it works. thanks for your time. it works now. – Nithy Kumar Feb 23 '15 at 02:42
0

use date_format function instead of str_to_date: SELECT id, user, date_format(timestamp, '%d/%m/%Y %h:%i %p') as timestamp FROM mytable WHERE user="bob";

aqteifan
  • 456
  • 1
  • 5
  • 17