date/time in sql

[ndedhia1]

This is my select statement:
[code=mysql]
SELECT
a2.conditionind ex, a1.timestamp,
a3.alarmdefinit ionname, a3.alarmdefinit ionseverity,
a2.tripsubjectn ame,a2.tripcont extname, a2.tripvalue,
a5.conditionnam e, a5.conditiontyp e
FROM alarmnotificati on AS a1,
alarmnotificati ondetail AS a2,
alarmdefinition AS a3,
alarmcondition AS a5
WHERE a1.notification id=a2.notificat ionid
AND a1.definitionid =a3.databaseide ntifier
AND a2.conditionid= a5.databaseiden tifier
ORDER BY a1.timestamp, a3.alarmdefinit ionseverity";[/code]

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 ALARMNOTIFICATI ON(
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.conditionind ex, 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:
[code=mysql]
SELECT DATE_FORMAT(tim estamp, '%r') AS 'Time'
FROM myTable
[/code]
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 ALARMNOTIFICATI ON(
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.conditionind ex, 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:
[code=php]
SELECT
a2.conditionind ex,
a1.timestamp,
a3.alarmdefinit ionname,
a3.alarmdefinit ionseverity,
a2.tripsubjectn ame,
a2.tripcontextn ame,
a2.tripvalue,
a5.conditionnam e,
a5.conditiontyp e
FROM alarmnotificati on AS a1,
alarmnotificati ondetail AS a2,
alarmdefinition AS a3,
alarmcondition AS a5
WHERE a1.notification id=a2.notificat ionid
AND a1.definitionid =a3.databaseide ntifier
AND a2.conditionid= a5.databaseiden tifier
ORDER BY a1.timestamp, a3.alarmdefinit ionseverity[/code]
the Create table code:
[code=mysql]
CREATE TEXT TABLE ALARMNOTIFICATI ON(
LOGGINGVERSION DECIMAL(4,2),
LOGGINGSTYLE CHAR(1),
NOTIFICATIONID INTEGER,
MILLISECONDS BIGINT,
TIMESTAMP TIMESTAMP,
ACTIVATIONID INTEGER,
DEFINITIONID INTEGER,
DEFINITIONSEVER ITY INTEGER,
CONSTRAINT PK_ALARMNOTIFIC ATION PRIMARY KEY(NOTIFICATIO NID)
);[/code]
the php code to desplay the tables:
[code=php]
while($row=mysq l_fetch_array($ result)){

fwrite($debug_h andle, $row['conditionindex '] . ",");
fwrite($debug_h andle, $row['timestamp'] . ",");
fwrite($debug_h andle, $row['alarmdefinitio nname'] . ",");
fwrite($debug_h andle, $row['alarmdefinitio nseverity'] . ",");
fwrite($debug_h andle, $row['tripsubjectnam e'] . ",");
fwrite($debug_h andle, $row['tripcontextnam e'] . ",");
fwrite($debug_h andle, $row['tripvalue'] . ",");
fwrite($debug_h andle, $row['conditionname'] . ",");
fwrite($debug_h andle, $row['conditiontype'] . ",");
fwrite($debug_h andle, "\r\n");

if ((substr($row['timestamp'],11,12)>=$timeF rame_min) && (substr($row['timestamp'],11,12)<$timeFr ame_max)) {
fwrite($handle, "<tr>");
fwrite($handle, "<td>" . $row['timestamp'] . "</td>");
fwrite($handle, "<td>" . $row['alarmdefinitio nname'] . "</td>");
if ($row['alarmdefinitio nseverity'] == 1)
fwrite($handle, "<td> High </td>");
elseif ($row['alarmdefinitio nseverity'] == 2)
fwrite($handle, "<td> Medium </td>");
elseif ($row['alarmdefinitio nseverity'] == 3)
fwrite($handle, "<td> Low </td>");
fwrite($handle, "<td>" . $row['tripsubjectnam e'] . "</td>");
fwrite($handle, "<td>" . $row['tripcontextnam e'] . "</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#NOTENOUGH INFO# </td>");
fwrite($handle, "</tr>");
}
}[/code]
the Java code that gets the time:
[code=java]
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) + ",");[/code]
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:
[code=php]
echo date("H:i:s", strtotime($row['timestamp']));[/code]

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