How to convert a mysql timestamp to a normal date?

[DarkRaven]

Hi out there !

 

Do you know how to convert a mysql timestamp to a normal date/time (e.g.: 13.11.2007-13:45) value ?

 

I've a mysql table in which I saved timestamp values in a VarChar(15) field. Today I know that there is a special type for timestamp fields instead of VarChar, but this is a different topic...

 

Now I have to create a report with iReport 2.0.2 in which the timestamp should be shown as a normal date/time value. I've "googled" but doesn't find something which can halp me about this topic.

 

Greets

Christian

[imypraz]

I use somthing like this when displaying dates...

 

new SimpleDateFormat("dd MMMM yyyy").format($F{report_date})

[DarkRaven]

Thank you for the fast reply !

 

I tried your example:

 

1. place a text field in my report

2. entering the data in the "edit expression" dialog (right mouse button)

3. inserting: new SimpleDateFormat("dd MMMM yyyy").format($F{logs_timestamp})

 

But I get an error:

 

Error evaluating expression: Source text : new SimpleDateFormat("dd.MM.yyyy").format($F{logs_timestamp})

java.lang.IllegalArgumentException: Cannot format given Object as a Date

 

What is wrong here ?

 

Post edited by: DarkRaven, at: 2007/11/20 12:27

Post edited by: DarkRaven, at: 2007/11/20 12:28

[DarkRaven]

Oh, I've forgotten to set up the textfield preferences...

 

If I switch it to java.util.Date there is an error, too: Cannot cast from String to Date. Is it possible to cast in the iReport software ?

Post edited by: DarkRaven, at: 2007/11/20 12:37

[Pedja]

The function format() returns string, your textfield should be java.lang.String.

[DarkRaven]

Hi !

 

Ok, but now I get the first error again: Cannot cast from Date to String. :S

[DarkRaven]

Here is the format I need the timestamp in: new SimpleDateFormat("dd.MM.yyyy - HH:mm : ss").parse($F{logs_timestamp}) (I've to enter two spaces here in the forum near "mm_:_ss", because instead I'll get the :S smily ! )

Post edited by: DarkRaven, at: 2007/11/20 13:55

[Pedja]

Try this

(new SimpleDateFormat("dd.MM.yyyy - HH:mm : ss")).format($F{logs_timestamp})

Post edited by: Pedja, at: 2007/11/20 15:00

[DarkRaven]

ErrorÂevaluatingÂexpressionÂ:Â ÂÂÂÂSourceÂtextÂ:ÂnewÂSimpleDateFormat("dd.MM.yyyyÂ-ÂHH:mmÂ:Âss")).format($F{logs_timestamp}

java.lang.IllegalArgumentException:ÂCannotÂformatÂgivenÂObjectÂasÂaÂDate ÂÂÂÂatÂjava.text.DateFormat.format(UnknownÂSource) ÂÂÂÂatÂjava.text.Format.format(UnknownÂSource)Â:pinch:

[DarkRaven]

Ok, this seems to be a problem now. The MySQL timestamps is a string variable like this: 1182757393 is it possible to use PHP in iReport to cast the values or is there a Java method to do this for me ? I ask, because I think the SimpleDateFormat is only able to use for "normal" date values like 11-30-2007 15-16 or something else.

Damn, it seems that I have to learn Java ! :laugh:

[DarkRaven]

PROBLEM SOLVED !!!

 

The last hint was the right one. Thank you ! :laugh:

 

You have to enter: new SimpleDateFormat("dd.MM.yyyy - HH:mm : ss")) .format($F{logs_timestamp} Then you have to change the database field type (in iReports) to java.sql.timestamp and then it works. Before the type was set to string as default.

 

Thank you to all people who helped me !

 

:woohoo:

[JamieDenman]

What if your SQL does not retrieve the date field from your MySQL DB? I.E SELECT COUNT(*) FROM XXX WHERE XXX.DATE BETWEEN $P{STARTDATE} AND $P{ENDDATE} the XXX.DATE is set to MySQL timestamp (in MySQL) what is iReports treating it as.

 

I have tried changing the parameter type in OpenReports but as I need a startdate and enddate for an Oracle query and for a MySQL query (in subreport) I need to manipulate the parameter to be able to use same selected date for both.

 

Oracle seems "happy" to use a date from the calendar in OpenReports but not so for MySQL. Uses (as string) 01-11-2007 but MySQL will work with 2007-11-01 (as string)

 

Is this a case of just manipulate the date as a string for MySQL rather than attempting to alter it to a timestamp in a different format.

[DarkRaven]

Hi !

 

What you discribe is a totaly different problem and I think you should open a new topic for this question.

In my case MySQL has a timestamp format like 1772332112 or something else. If you save this in your DB as a string, you'll get problems while converting this timestamp in Java. But If you change your DB field type to "timestamp", Java is able to calculate with it. That's the solution in my case.

 

Hope you'll get help soon ! ;)

 

CU

Christian