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]

alexs wrote:

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?

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 <[e-mail]:

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?

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

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

mysqlSELECT 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 <[e-mail]:

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).

mysqlCREATE TABLE `network_time` ( `col1` int(11) default NULL )
ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

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

mysqlSELECT 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]

Serman D. wrote:

On Apr 17, 1:54 pm, alexs <[e-mail]:

>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).

mysqlCREATE TABLE `network_time` ( `col1` int(11) default NULL )
ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

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

mysqlSELECT 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)

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)

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

[alexs]

On Apr 18, 12:42*pm, Serge Rielau <[e-mail]:

Serman D. wrote:

On Apr 17, 1:54 pm, alexs <[e-mail]:

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).

mysqlCREATE TABLE `network_time` ( * `col1` int(11) default NULL )
ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

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

mysqlSELECT 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)

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)

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

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]

alexs wrote:

On Apr 18, 12:42 pm, Serge Rielau <[e-mail]:

>Serman D. wrote:

>>On Apr 17, 1:54 pm, alexs <[e-mail]:
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).
mysqlCREATE TABLE `network_time` ( `col1` int(11) default NULL )
ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysqlINSERT INTO network_time VALUES (3600),(5400),(7200),(43200),
(86400);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysqlSELECT 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)

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)

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

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

OK, then that second function should do the job. Trivial to extend to
show days/weeks as a unit as well.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab