I have a field in mysql table which is storing the information as string like this
Time::[Mon,16 Jun 2009 00:52:06 GMT]
Now i want to display
24 jun 2009
How do i do this?
Date time changing of the string
Moderator: General Moderators
- Peter Anselmo
- Forum Commoner
- Posts: 58
- Joined: Wed Feb 27, 2008 7:22 pm
Re: Date time changing of the string
First, That's a very non-standard way to store a date in the database. The two most popular ways are with unix timestamps, or MySql Datetimes (YYYY-MM-DD HH:MM:SS).
That being said, substr() should do the trick in your example:
That being said, substr() should do the trick in your example:
Code: Select all
$date = Time::[Mon,16 Jun 2009 00:52:06 GMT]
$newDate = substr($date, 11, 8) //16 Jun 2009 (if I counted right)Re: Date time changing of the string
@Peter .. yes i agree with you.. thats a very non-standard way of putting time. It was a general logger which was supposed to log any information that it gets. So i had no idea this field will contain time and that too sent like this. After about 1 lakh insertions, i was supposed to write this. I figured out the same in mysql. but i dont think it will be useful for anyone.
Here is the query that worked,
Here is the query that worked,
Code: Select all
SELECT STR_TO_DATE(substr(f2,INSTR(f2,"[")+5,INSTR(f2,"]")-INSTR(f2,"[")-18), "%d %b %Y") as mydate,
count(DISTINCT(f3)) as userstoday, count(DISTINCT(f5)) as channels FROM `tbl_general_log` group by mydate order by mydate