How to convert the Date into Epoch in iReport and Jasperserver.

[ungalnanban]

I want to create a report for login history between two dates.

The login history table has the following fields
login_date  (data type is integer)
user_id     (data type is integer)



Sample records

|login_date    |user_id |
+----------------+----------+
|1299954600 |   105 |
|1299954600 |   105 |
|1299954600 |   105 |
|1299954600 |   105 |
|1301164200 |   114 |
|1301164200 |   106 |
|1301769000 |   110 |
|1301164200 |   106 |
|1301769000 |   106 |
|1301769000 |   106 |
|1301769000 |   106 |
|1302978600 |   102 |
|1302373800 |   112 |
|1302978600 |   111 |
|1302978600 |   111 |
|1302978600 |   111 |


Note: I have stored the login date in Epoch time

I want to create the report for following query.

SELECT user_id,count(user_id) from rep_time_tracking where login_date between 1301596200
and 1303496940 group by 1;

I have added two parameters for run time parameter.
Example. ${FROM} and $P{TO}, value expression is Date/Time.


Now user will select the date and time using the input I want to convert into Epoch.

How to achieve this in iReport as well as JasperServer ?

 

[jpauze]

Don't really deal with Epoch dates but let me give it a shot and see if it helps point you forward. Epoch is seconds since a reference date, sometime back in 1970 if I recall correctly, luckily for you, java stores the same internal format but in milliseconds so it should be a simple excercise of converting the java date to seconds.

 

$P{FROM}.getTime() / 1000 or $P{TO}.getTime() / 1000

 

Alternatively, you can convert your epoch date in the sql statement directly to convert to milliseconds (* 1000)

 

There are numerous posts on here about using parameters dynamically within SQL statements so won't repeat the info and should be a simple search.

 

Hope this helped.