MySQL Date Functions

Working with date in MySQL is very important for date based query processing. today we going to get some important MySQL date functions frequently used in projects.

Here is the date functions with example queries from Article table  have the column date_of_post

CURDATE() - Return the current date

Example:

SELECT * FROM articles WHERE date_of_post = CURDATE()

 

DAY()   - Return day number from the date column

Example:
SELECT * FROM article WHERE DAY(date_of_post) = 1

 

MONTH() - Return Month number from the date column

Example:
SELECT * FROM article WHERE MONTH(date_of_post) = 2

 

YEAR() – Return year number from the date column

Example:
SELECT * FROM article WHERE MONTH(date_of_post) = 2011

 

LAST_DAY() -  Return Last date as number, From the month of the date column

Example:
SELECT * FROM article WHERE DAY(date_of_post) = LAST_DAY(date_of_post)

 

DAYNAME() - Return day name as string from the date column

SELECT DAYNAME (date_of_post) AS `day_of_post` FROM article

 

MONTHNAME() - Return Month name as string from date column

Example:
SELECT MONTHNAME(date_of_post) as `month_of_post` FROM article

 

QUARTER() - Return Quarter Number as 1 to 4 Format as number

Example:
SELECT QUARTER(date_of_post) AS `post_quarter` FROM article

 

WEEK() - Return Week Number Number (0-53)

Example:
SELECT WEEK(date_of_post) AS `week ` FROM article

Get Yahoo contacts from address book

Mr. Antony request me how to list all email ids from user's yahoo email account. here is the simple solution to get all Emails from user contact list in yahoo address book.

In order to work with Yahoo Address book we need to create oauth Project on yahoo developer network. let us create it first.

Step: 1

Goto : http://developer.yahoo.com/

Select My Projects on Top Right below the search box. (You are ask to Login with existing yahoo account)

Step: 2

 Click New Project Button

Step 3:

Select Application Type "Standard"

Step 4: ( Fill your Application as per the form below with your own values)

Step 5: (Get your Project Keys)

Application ID: below the Project Name

Consumer Key and Consumer Secret Inside the Box

Step 6: (Verify your Domain)

 

Step: 7 (Download Yahoo PHP SDK -  Included in Demo Download)

Goto: http://developer.yahoo.com/social/sdk/php/

index.php ( Show the Button labled "Get Yahoo Contacts")

<p align="center"><br />

<a href="getContact.php" style="background:#939; color:#FFF; 
padding:10px; font-family:'Trebuchet MS', Arial, Helvetica, sans-serif; 
text-decoration:none; font-size:14px; font-style:italic; margin-top:50px; 
text-align:center;">Get Yahoo Contacts</a></p>

getContacts.php

Get List of contacts using YQL after user accesshttp://developer.yahoo.com/social/rest_api_guide/contacts_table.html

<?php session_start();

error_reporting(0);

// Replace your Own KEYS 

require("lib/Yahoo.inc");
// Your Consumer Key (API Key) goes here.
define('OAUTH_CONSUMER_KEY', "j0yJmk9M1Zzc0F3YURkV3AwJmQ9WVdrOU5tMVRhSFJETjJNbWNHbzlNVEUxTXpRek9UUTJNZy0tJnM9Y29uc3VtZXJzZWNyZXQmeD0wMg--");
// Your Consumer Secret goes here.
define('OAUTH_CONSUMER_SECRET', "febded04011a8e6e2f1227b9e24688f2252587a");
// Your application ID goes here.
define('OAUTH_APP_ID', "mShtC7c");
// Call back URL
define('CALL_BACK_URL','http://demos.w3lessons.com/yahoo-contact/getContact.php');

/*
    Consumer Key:
    Consumer Secret:
    Application URL:
    App Domain:

   dj0yJmk9M1Zzc0F3YURkV3AwJmQ9WVdrOU5tMVRhSFJETjJNbWNHbzlNVEUxTXpRek9UUTJNZy0tJnM9Y29uc3VtZXJzZWNyZXQmeD0wMg--
    afebded04011a8e6e2f1227b9e24688f2252587a
    http://demos.w3lessons.com/yahoo-contact/index.php
    demos.w3lessons.com
 
*/ 

$session = YahooSession::requireSession(OAUTH_CONSUMER_KEY, OAUTH_CONSUMER_SECRET, OAUTH_APP_ID);   

$user = $session->getOwner();

$query = sprintf("select * from social.contacts where guid=me;");  
$response = $session->query($query); 



if(isset($response)){

   foreach($response->query->results->contact as $id){

       foreach($id->fields as $subid){

               if( $subid->type == 'email' )
               $emails[] = $subid->value;
       }
   }
}

$session->clearSession();

echo "<pre>";
print_r($emails);
?>
<br />
<br />
<a href="index.php">Back</a>

Live Demo

Download Script