[SOLVED] day of week from MySQL date

[edpatterson]

I am trying to get the 3 letter abbreviation for the day from a datetime value retrieved from MySQL. The datetime values are returned as strings, 'YYYY-MM-DD HH:MM:SS'

 

I am trying to make a daily weight & blood sugar page and want the abbreviated days for the x-plot (jpgraph, a way too cool toy).

 

A side question. Is there an access level that bypasses the Verification codes? I have inner ocular lens implants (too much karate in a younger life) which makes it REAL hard to see colored text on colored backgrounds. I tried the 'Listen to the letters' option which does not play on my Linux laptop. I know, whine, whine, whine :-)

 

[Maq]

I am trying to get the 3 letter abbreviation for the day from a datetime value retrieved from MySQL. The datetime values are returned as strings, 'YYYY-MM-DD HH:MM:SS'

 

Read this - DATE_FORMAT().

 

A side question. Is there an access level that bypasses the Verification codes?

 

Sorry, verification codes for what?

[PFMaBiSmAd]

I think he means the CAPTCHA that must be entered when you have less than so many posts (10 if I remember correctly.)

[Maq]

I think he means the CAPTCHA that must be entered when you have less than so many posts (10 if I remember correctly.)

 

Oh yes, that's right.  Sorry, I forgot about that.  Only an Admin or Mod can help you there.

[edpatterson]

Thanks, I had not thought about making a formatted query. I'll see if I can get the results of 30 days worth of data and still keep it in order.

 

<flip, another 'The letters you typed don't match the letters that were shown in the picture'. />

[edpatterson]

arg, another 'The letters you typed don't match...' message, make that 3 of them...

I just noticed this was moved from PHP to MySQL. Perhaps I did not make my question clear.

 

I query the datetime values out of MySQL via PHP and need to use PHP to display it as PHP's date('D',$timestamp) value, 3 letter abbreviation of the day of the week.

 

After failing miserably at trying to get a query out of MySQL that automagically converted my data I came up with the following

 

Now the question is, what is a more efficient way to convert a MySQL datetime value into the 3 letter abbreviation which represents the day of the week for each record returned.

 

<?php
  $mysqlDate = "2009-05-23 14:48:13";
  echo str_pad("MySQL Date: ",14," ",STR_PAD_LEFT).$mysqlDate."\n";
  list($year, $month, $day, $hour, $minute, $second) = split('[-\:]',$mysqlDate);
  $epoch = mktime($hour, $minute, $second, $month, $day, $year);
  echo str_pad("Epoch: ",14," ",STR_PAD_LEFT).$epoch."\n";
  echo str_pad("DOW: ",14," ",STR_PAD_LEFT).date('D',$epoch)."\n";
  echo mysqlDateToEpoch($mysqlDate)."\n";
  echo date('D',mysqlDateToEpoch($mysqlDate))."\n";

  function mysqlDateToEpoch($mysqlDate){
    list($year, $month, $day, $hour, $minute, $second) = split('[-\:]',$mysqlDate);
    return(mktime($hour, $minute, $second, $month, $day, $year));
  }
?>

 

[Ken2k7]

Use DATE_FORMAT() and SUBSTR().

[PFMaBiSmAd]

The most efficient way was already given by Maq in the first reply in the thread - the mysql DATE_FORMAT() function, possibly with an alias attached to make accessing the produced value easier, IS all that you need.

 

P.S. The light-blue link he posted is to the mysql documentation for the function.

[edpatterson]

Thanks, I did go and read the section but did not see how it applied to what I need. I am not a DBA by any far stretch of the imagination and only know the most basic select statements.

 

I will go read up on 'aliases' to see what I am missing.

 

Thanks,

Ed

The most efficient way was already given by Maq in the first reply in the thread - the mysql DATE_FORMAT() function, possibly with an alias attached to make accessing the produced value easier, IS all that you need.

 

P.S. The light-blue link he posted is to the mysql documentation for the function.

 

[Ken2k7]

You don't need aliases. Just follow the examples on that page and you should be fine. You don't have to be an expert to know how to follow a few examples and figure out how to put something fairly simple as this in your code. It may seem hard because you never used it before.

[edpatterson]

What was throwing me off was all the examples were using static values. A simple nudge would have helped a lot. This is what I came up with.

select left(dayname(dt),3) from measurments where dt > date_add(now(), interval -7 day) order by dt;

You don't need aliases. Just follow the examples on that page and you should be fine. You don't have to be an expert to know how to follow a few examples and figure out how to put something fairly simple as this in your code. It may seem hard because you never used it before.

[Maq]

What was throwing me off was all the examples were using static values. A simple nudge would have helped a lot. This is what I came up with.

select left(dayname(dt),3) from measurments where dt > date_add(now(), interval -7 day) order by dt;

 

Nice, so does that do exactly what you want?

[edpatterson]

Nice, so does that do exactly what you want?

Yes, I am trying to find the magic thing to check which turns the topic green and marks it solved

 

[PFMaBiSmAd]

The Topic Solved button is on the bottom left (I'll get it for you since you are currently off line.)