By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,473 Members | 3,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,473 IT Pros & Developers. It's quick & easy.

converting from time in seconds to timestamp format

P: n/a
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
Jun 27 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
Jun 27 '08 #2

P: n/a
On Apr 17, 1:54 pm, alexs <A.Sha...@hull.ac.ukwrote:
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.
Jun 27 '08 #3

P: n/a
On Apr 17, 1:54 pm, alexs <A.Sha...@hull.ac.ukwrote:
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.
Jun 27 '08 #4

P: n/a
Serman D. wrote:
On Apr 17, 1:54 pm, alexs <A.Sha...@hull.ac.ukwrote:
>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
Jun 27 '08 #5

P: n/a
On Apr 18, 12:42*pm, Serge Rielau <srie...@ca.ibm.comwrote:
Serman D. wrote:
On Apr 17, 1:54 pm, alexs <A.Sha...@hull.ac.ukwrote:
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
Jun 27 '08 #6

P: n/a
alexs wrote:
On Apr 18, 12:42 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>Serman D. wrote:
>>On Apr 17, 1:54 pm, alexs <A.Sha...@hull.ac.ukwrote:
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
Jun 27 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.