Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select Distinct month and year from mysql timestamp field and echo them in php

Tags:

php

mysql

My mysql table has a createdOn column with filedtype 'timestamp' in the format of 2011-10-13 14:11:12.

What I need is to show, is distinct month,year from createdOn column.

I have searched on stackover flow and was able to echo back months using following code,

*$sqlCommand = "SELECT DISTINCT MONTH(createdOn) AS 'Month' FROM videoBase ORDER BY createdOn DESC";
$query=mysqli_query($myConnection,$sqlCommand) or die(mysqli_error());
while($row = mysqli_fetch_array($query)) {
    $date = date("F", mktime(0, 0, 0, $row['Month']));
    echo ''.$date.' <br />';
}*

This outputs months as :

October
January

What I need is the output in the format of:

October 2011
January 2012

Can anybody please let me know, what changes I should make in the code in order to get the required output.

Thanks

like image 913
Code Road Avatar asked Feb 22 '12 13:02

Code Road


People also ask

How do I get month from date in MySQL?

MONTH() function in MySQL is used to find a month from the given date. It returns 0 when the month part for the date is 0 otherwise it returns month value between 1 and 12.

How do I get just the year from a timestamp?

Use the YEAR() function to retrieve the year value from a date/datetime/timestamp column in MySQL. This function takes only one argument – a date or date and time. This can be the name of a date/datetime/timestamp column or an expression returning one of those data types.

How do I select a date from a timestamp in SQL?

In MySQL, use the DATE() function to retrieve the date from a datetime or timestamp value. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a timestamp/datetime column.

How do I select unique dates in SQL?

You need to use DISTINCT keyword to select distinct dates from datetime column in a table. Now you can insert some records in the table using insert command. Display all records from the table using select statement.


2 Answers

For a MySQL solution:

SELECT DISTINCT CONCAT(MONTHNAME(createdOn), ' ', YEAR(createdOn)) AS `Month`
FROM videoBase
ORDER BY createdOn DESC

This takes the output of the MONTHNAME() function and YEAR() function and concatenates them with a space between, like this:

October 2011
January 2012
like image 115
Marcus Adams Avatar answered Sep 28 '22 15:09

Marcus Adams


Use this:

$date = date("F Y", strtotime($row['Month']));

and in your query don't select the month, just:

SELECT DISTINCT createdOn AS 'Month' FROM videoBase ...

So it will be:

$comma = '';
while($row = mysqli_fetch_array($query)) {
    $date = $comma . date("F", mktime(0, 0, 0, $row['Month']));
    echo $comma . $date;
    $comma = ', ';
}
like image 38
EscoMaji Avatar answered Sep 28 '22 16:09

EscoMaji