converting from time in seconds to timestamp format

[alexs]

hi,

I'm moving a mysql database over to using db2 V9.5

The database is used by our radius server to store network accounting
information from our switches.
One parameter is the length of a network session in seconds.

In mysql there is a sec_to_time function which i then used to store
the session time as a char string
I want to do something similar in db2 and thought that the best way
would be to store it in a timestamp column.

Can i do that?

any better way of storing the time?

TIA
alex

[Serge Rielau]

Store time as TIME?
When you refer to "seconds" do you mean:
* Seconds since midnight,
* seconds since 1972
* seconds since....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serman D.]

On Apr 17, 1:54 pm, alexs <A.Sha...@hull.ac.uk> wrote:

> In mysql there is a sec_to_time function which i then used to store
> the session time as a char string

You mean you are currently doing something like this?

mysql> CREATE TABLE network_time (
-> seconds int(11) default NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO network_time VALUES (3600),(5400),(7200),(43200),
(86400);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT sec_to_time(seconds) AS elapsed FROM network_time;
+----------+
| elapsed |
+----------+
| 01:00:00 |
| 01:30:00 |
| 02:00:00 |
| 12:00:00 |
| 24:00:00 |
+----------+
5 rows in set (0.00 sec)

--
Serman D.

[Serman D.]

On Apr 17, 1:54 pm, alexs <A.Sha...@hull.ac.uk> wrote:

> In mysql there is a sec_to_time function which i then used to store
> the session time as a char string

Are you doing something like below? I guess you can continue to store
elapsed seconds as an integer and create a function yourself to do the
conversion to 'hh:mm:ss' (unless it already exists in DB2).

mysql> CREATE TABLE `network_time` ( `col1` int(11) default NULL )

ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO network_time VALUES (3600),(5400),(7200),(43200),
(86400);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT sec_to_time(col1) AS elapsed FROM network_time;

+----------+
| elapsed |
+----------+
| 01:00:00 |
| 01:30:00 |
| 02:00:00 |
| 12:00:00 |
| 24:00:00 |
+----------+

5 rows in set (0.01 sec)

--
Serman D.

[Serge Rielau]

OK, DB2 has a midnight_seconds() fucntion to tuen time into seconds but
not the inverse.
But.. That's easily fixed:
CREATE FUNCTION SEC_TO_TIME(arg INTEGER) RETURNS TIME CONTAINS SQL
NO EXTERNAL ACTION DETERMINISTIC
RETURN TIME('00:00:00')
+ (arg / 3600) HOURS
+ MOD(arg / 60, 60) MINUTES
+ MOD(arg, 3600) SECONDS;

Note that this is NOT an INTERVAL. So 24 hours is as far as it can hold.
You could return astring such as:
RETURNS VARCHAR(12)...
RETURN TRIM(CHAR(arg / 3600)) || ':'
|| TRIM(CHAR(MOD(arg / 60, 60)) || ':'
|| TRIM(MOD(arg, 3600));

That's plenty of hours..

(All untested)

[alexs]

sorry didn't make myself clear. The variable is a radius server
accounting attribute and is an elapsed time i.e. the number of seconds
since the radius server sent a start of session accounting packet.
Some of our students log onto the network and never log off so we can
see elapsed times of days weeks .....

Many thanks for the above
Alex

[Serge Rielau]

OK, then that second function should do the job. Trivial to extend to
show days/weeks as a unit as well.