date/time in sql

[ndedhia1]

This is my select statement:

SELECT 
  a2.conditionindex, a1.timestamp, 
  a3.alarmdefinitionname, a3.alarmdefinitionseverity, 
  a2.tripsubjectname,a2.tripcontextname,  a2.tripvalue,
  a5.conditionname, a5.conditiontype 
FROM alarmnotification AS a1, 
     alarmnotificationdetail AS a2, 
     alarmdefinition AS a3, 
     alarmcondition AS a5 
WHERE a1.notificationid=a2.notificationid 
AND a1.definitionid=a3.databaseidentifier 
AND a2.conditionid=a5.databaseidentifier 
ORDER BY a1.timestamp, a3.alarmdefinitionseverity";

right now, time stamps is displayed: month/day/year hour/min/sec am/pm

MY QUESTIONS IS:
I want my timestamp to only display hour/min/sec

how do I change my select statement.

thank you

[Atli]

Hi.

Try the DATE_FORMAT function.

Should allow you to convert any date/time type field into whatever format you want.

[ndedhia1]

should I use the date_format in the create table for example,

CREATE TEXT TABLE ALARMNOTIFICATION(
TIMESTAMP DATE_FORMAT('HH:MM:SS'),

[mwasif]

No, it will be used at the time of SELECTing data. Did you look at the link provided by Atli?

[ndedhia1]

I used it in the select statement like this:
"SELECT a2.conditionindex, DATE_FORMAT(a1.timestamp, '%r'),...
but it only changed the header of the table and not the contents inside the table.

thanks

[Atli]

This wouldn't change the actual data stored in your table.
It would change the way the data is returned by the SELECT statement.

That is, if these were the actual values inside your table:

mysql> SELECT Timestamp FROM myTable;
+---------------------+
| Timestamp           |
+---------------------+
| 2009-01-28 08:45:00 | 
| 2009-01-28 13:45:00 | 
| 2009-01-28 03:45:00 | 
| 2009-01-28 20:45:00 | 
+---------------------+

And you execute this query:

SELECT DATE_FORMAT(timestamp, '%r') AS 'Time'
FROM myTable
 

You would get:

mysql> SELECT DATE_FORMAT(timestamp, '%r') AS 'Time'
     > FROM myTable;
+-------------+
| Time        |
+-------------+
| 08:45:00 AM | 
| 01:45:00 PM | 
| 03:45:00 AM | 
| 08:45:00 PM | 
+-------------+

[ndedhia1]

another questions:
if in my create table, I want to do it so that there is only hh:mm:ss for a field..do i do it something like this:

CREATE TEXT TABLE ALARMNOTIFICATION(
TIMESTAMP DATETIME(hh:mm:ss),

[Atli]

No, you would use the TIME data type.

The Reference Manual contains a list of all available data types, as well as everything else you may want to know about the correct usage of MySQL syntax.

[ndedhia1]

What i ment when i said that the data is changed is that when I run:
"SELECT a2.conditionindex, DATE_FORMAT(a1.timestamp, '%r'),...
the file that I am using to print this select statement comes back empty with only the header line being there with the titles of the fields. All of the data is not showing up

[Atli]

Ok. Then there is either no data fitting your query, or your code is not displaying it properly.

Either way, we would need to see the code to be able to help.
It's damn hard to debug code you can't see :)

[ndedhia1]

There is definitely Data that fits the query, because if comment out if statements that deal with the timestamp, the rest of the fields are populated. This code that i am sending you is the original that displays the output in an excel file but with the timestamp, displays the date, and hour and min. Everything is pretty much correct but I want the timestamp to only display the hr, min and seconds. No date.
Here is parts of the code:

The select statement:

SELECT 
    a2.conditionindex, 
    a1.timestamp, 
    a3.alarmdefinitionname, 
    a3.alarmdefinitionseverity, 
    a2.tripsubjectname, 
    a2.tripcontextname, 
    a2.tripvalue, 
    a5.conditionname, 
    a5.conditiontype 
FROM alarmnotification AS a1, 
     alarmnotificationdetail AS a2, 
     alarmdefinition AS a3, 
     alarmcondition AS a5 
WHERE a1.notificationid=a2.notificationid 
  AND a1.definitionid=a3.databaseidentifier 
  AND a2.conditionid=a5.databaseidentifier 
ORDER BY a1.timestamp, a3.alarmdefinitionseverity

the Create table code:

CREATE TEXT TABLE ALARMNOTIFICATION(
LOGGINGVERSION DECIMAL(4,2),
LOGGINGSTYLE CHAR(1),
NOTIFICATIONID INTEGER,
MILLISECONDS BIGINT,
TIMESTAMP TIMESTAMP,
ACTIVATIONID INTEGER,
DEFINITIONID INTEGER,
DEFINITIONSEVERITY INTEGER,
CONSTRAINT PK_ALARMNOTIFICATION PRIMARY KEY(NOTIFICATIONID)
);

the php code to desplay the tables:

while($row=mysql_fetch_array($result)){
 
        fwrite($debug_handle, $row['conditionindex'] . ",");
        fwrite($debug_handle, $row['timestamp'] . ",");
        fwrite($debug_handle, $row['alarmdefinitionname'] . ",");
        fwrite($debug_handle, $row['alarmdefinitionseverity'] . ",");
        fwrite($debug_handle, $row['tripsubjectname'] . ",");
        fwrite($debug_handle, $row['tripcontextname'] . ",");
        fwrite($debug_handle, $row['tripvalue'] . ",");
        fwrite($debug_handle, $row['conditionname'] . ",");
        fwrite($debug_handle, $row['conditiontype'] . ",");
        fwrite($debug_handle, "\r\n");
 
        if ((substr($row['timestamp'],11,12)>=$timeFrame_min) && (substr($row['timestamp'],11,12)<$timeFrame_max)) {
            fwrite($handle, "<tr>");
            fwrite($handle, "<td>" . $row['timestamp'] . "</td>");
            fwrite($handle, "<td>" . $row['alarmdefinitionname'] . "</td>");
            if ($row['alarmdefinitionseverity'] == 1)
                fwrite($handle, "<td> High </td>");
            elseif ($row['alarmdefinitionseverity'] == 2)
                fwrite($handle, "<td> Medium </td>");
            elseif ($row['alarmdefinitionseverity'] == 3)
                fwrite($handle, "<td> Low </td>");
            fwrite($handle, "<td>" . $row['tripsubjectname'] . "</td>");
            fwrite($handle, "<td>" . $row['tripcontextname'] . "</td>");
            if ($row['tripvalue'] == 1)
                fwrite($handle, "<td> DOWN </td>");
            else
                fwrite($handle, "<td>" . $row['tripvalue'] . "</td>");
            fwrite($handle, "<td>" . $row['conditionname'] . "</td>");
            if ($row['conditiontype'] == 1)
                fwrite($handle, "<td> Instrumentor </td>");
            elseif ($row['conditiontype'] == 2)
                fwrite($handle, "<td> Process Watcher </td>");
            else
                fwrite($handle, "<td> ERROR#NOTENOUGHINFO# </td>");
            fwrite($handle, "</tr>");
        }
    }

the Java code that gets the time:

pw.print(c.get(c.YEAR) + "-" + (c.get(c.MONTH)+1) + "-" + c.get(c.DAY_OF_MONTH) + " " + c.get(c.HOUR_OF_DAY) + ":" + c.get(c.MINUTE) + ":" + c.get(c.SECOND) + ",");

hope that helps a bit.

thanks

[Atli]

Ok, so I'm assuming the output you need to change is in line #16 of your PHP code?

If so, there are two ways I see.

First, apply the DATE_FORMAT function to your SELECT query, like we have been discussing.
You would have to reflect this change in your if statement on line #14 to. It is expecting the the standard datetime format, so it probably won't work after the format.

Or, you could use the PHP date function in line #16 to alter the timestamp before you print it. This would not require you to actually alter the query, so your timestamp would remain intact if it has to be used elsewhere.
Note that you will have to run the timestamp through strtotime to get the Unix timestamp before you run it through the date function.

Like:

echo date("H:i:s", strtotime($row['timestamp']));

P.S.
Where does that Java code fit in?
You know, PHP can do that to:

echo date("Y-m-d H:i:s", time());

[ndedhia1]

Thanks for the help,
That made the correct conversion.

thank you