Selecting Date from Current_timestamp Field

Hi Everyone,
I am creating a mySQL table where one column is last_updated.  I have that as timestamp Type and default CURRENT_TIMESTAMP.  So as people fill out the form to get on the list it marks when they filled out the form.

Through Dreamweaver I am creating a page where the members of the list and shown with the last_updated column.  My issue is I only want the date and not the time to populate the field.  

I have tried DATE_FORMAT('%m %d %y', last_updated) AS DATE and for my table code I have
<td><?php echo $row_Recordset3['DATE']; ?></td>  

It is not working but I am getting no errors just nothing shows up in the last_updated column on the page showing the members of the list.

I am not sure if I have to first separate the date and time from the CURRENT_TIMESTAMP data.  

Any suggestions would be appreciated, thanks in advance.
dlojAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Please see this article for more information on using DATETIME values in PHP and MySQL.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

This SELECT should get you the ISO-8601 format date, which is the right format for all internal representations.
SELECT DATE_FORMAT(date, '%Y-%m-%dT%TZ') FROM table_name
0
dlojAuthor Commented:
Thanks Ray but that didn't work.  I am still getting nothing in the last_updated column when I enter this sql in phpmyadmin to test:

SELECT first_name, last_initial, phone, email, country, lyears, lmo, ayears,
prg, sponsor, speaker, speaker_phone, outreach_calls, lib, web_infor,
DATE('m/d/Y', 'last_updated') AS DATE from resource_list WHERE web_infor = 'yes'

I get all the information for the members that want to be on the web but do not get  any CURRENT_TIMESTAMP info at all.

Now when I put:

SELECT * FROM `resource_list` WHERE `web_infor`= 'yes';

I get all the members and also get the CURRENT_TIMESTAMP set by default when they entered their info on the form.   I just want the date though and that is my dilema.  

Thanks again for your response.
0
arnoldCommented:
You need to use from_unixtime(last_updated) as the input to your format string
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Ray PaseurCommented:
This worked for me.  Sorry for the long example; most of it is setup for the SELECT query on line 78.
http://iconoun.com/demo/temp_dloj.php

Expected output:
Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Walter
            [abcd] => 06-07-2015
        )

    [1] => Array
        (
            [id] => 2
            [name] => Ray
            [abcd] => 06-07-2015
        )

    [2] => Array
        (
            [id] => 3
            [name] => Marco
            [abcd] => 06-07-2015
        )

)

Open in new window

<?php // demo/temp_dloj.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = [['name' => 'Walter'], ['name' => 'Ray'], ['name' => 'Marco']];


// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

$dsn = "mysql:host=$db_host;dbname=$db_name";
try {
    $pdo = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $exc) {
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}
// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, name  VARCHAR(24) NOT NULL DEFAULT ''
, abcd  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
)
"
;

// RUN QUERY TO CREATE THE TABLE
try {
    $pdos = $pdo->query($sql);
}
catch(PDOException $exc) {
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}


// PREPARE THE QUERY TO LOAD THE NAMES ("PREPARE" ONLY NEEDED ONCE)
$sql = "INSERT INTO my_table ( name ) VALUES ( :name )";
try {
    $pdos = $pdo->prepare($sql);
}
catch(PDOException $exc) {
    var_dump($exc);
}


// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // USE THE ARRAY OF KEYWORD => VALUE TO ATTACH name
    try {
        $pdos->execute($person);
    }
    catch(PDOException $exc) {
        var_dump($exc);
        trigger_error($exc->getMessage(), E_USER_ERROR);
    }
}


// QUERY TO RETRIEVE THE ROWS FROM THE DATABASE TABLE
try {
    $pdos = $pdo->query("SELECT id, name, DATE_FORMAT(abcd,'%m-%d-%Y') AS abcd FROM my_table");
    $rows = $pdos->fetchAll(PDO::FETCH_ASSOC);
    print_r($rows);
}
catch(PDOException $exc) {
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arnoldCommented:
Dloj,

Could you output the raw data from last_update column along with the show create table to see what the definition of the last_updated column is.
0
dlojAuthor Commented:
Hi Arnold,

Here is my show create table for my resource_list table;

 resource_list | CREATE TABLE `resource_list` (
  `debtors_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) DEFAULT NULL,
  `last_initial` varchar(4) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `lyears` int(5) DEFAULT NULL,
  `lmo` int(5) DEFAULT NULL,
  `ayears` varchar(20) DEFAULT NULL,
  `prg` varchar(4) DEFAULT NULL,
  `sponsor` varchar(4) DEFAULT NULL,
  `speaker` varchar(4) DEFAULT NULL,
  `speaker_phone` varchar(4) DEFAULT NULL,
  `outreach_calls` varchar(4) DEFAULT NULL,
  `lib` varchar(20) DEFAULT NULL,
  `web_infor` varchar(5) DEFAULT NULL,
  `last_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`debtors_id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 |
0
Ray PaseurCommented:
Given this line in the CREATE TABLE...
`last_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

You can probably map that to the column named `abcd` in the code example here:
http://www.experts-exchange.com/Database/MySQL/Q_28687058.html#a40817203
0
dlojAuthor Commented:
Thanks Ray,

I had the column name and the date format %m %d %y as you have shown reversed.
0
Ray PaseurCommented:
Thanks for the points and thanks for using E-E -- those positional arguments often make it hard to remember the order for functions.  I've got an example of how I try to deal with that (Needle, haystack? Haystack, needle?) in this article:
http://www.experts-exchange.com/articles/18409/Using-Named-Parameters-in-PHP-Function-Calls.html

Best regards, ~Ray
0
Ray PaseurCommented:
Now please do me a favor... Since I posted a tested and working code example, please read the grading guidelines and explain why you marked the grade down.  What were you expecting that you did not get?  Why didn't you give a grade of "A" here?
http://support.experts-exchange.com/customer/portal/articles/481419
0
dlojAuthor Commented:
Hi Ray, Well the answer was to reverse the items in the parenthesis which you obviously knew but you chose to fill the answer with a long example.   Which actually covered the answer at the time.  I found the answer elsewhere actually but went back and saw that you had given the same answer so I gave you the credit.  The long answer was unnecessary, sometimes simple is best.
0
Ray PaseurCommented:
you obviously knew but you chose to fill the answer with a long example
Which was necessary for me to test the answer before I posted it.  There is a big difference between thinking you know an answer and getting the correct results.  I even told you the exact line number to look at.

Did you read the Experts-Exchange grading guidelines?  If you give a marked-down grade for a perfect answer, what are we to make of that?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.