Page 1 of 1

Date time changing of the string

Posted: Sat Jul 04, 2009 8:47 am
by susrisha
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?

Re: Date time changing of the string

Posted: Sat Jul 04, 2009 12:10 pm
by Peter Anselmo
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:

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

Posted: Mon Jul 06, 2009 2:45 am
by susrisha
@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,

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