conversions part 2: ip addresses stored as decimal

Leave a comment

My previous conversion post covered unix epoch date conversion. Here I discuss my adventures with converting decimal ip addresses.

Internet IP v 4 addresses look like this 41.22.154.123. 4 numbers separated by periods. Each number ranges from 0 to 255. I never knew it, but these addresses are commonly stored as binary or as decimal numbers. One reason is to facilitate sorting the addresses. There’s no good way to sort the addresses in the dotted notation.

Here’s the function I ended up using with Oracle PL/SQL to convert the decimal value to dotted.

create or replacefunction inttoip(ip_address integer) return varchar2
deterministic
is
begin    
return to_char(mod(trunc(ip_address/256/256/256),256)) ||'.'||to_char(mod(trunc(ip_address/256/256),256))           ||'.'||to_char(mod(trunc(ip_address/256),256))           ||'.'||to_char(mod(ip_address,256));
end;

A colleague sent to me some SQL Server and Oracle conversions using a slightly different approach. I’ve copied these below. And of course, google searches will quickly direct you to other material in this area.

-- MS SQL Server with convert to bigint
SELECT dbo.IPADDRESS.IPADDRESS,
CAST(ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + 
CAST((cast(dbo.IPADDRESS.IPADDRESS as bigint) % 256 ) AS varchar(4)) as IPDottedNotation
FROM  dbo.IPADDRESS

-- MS SQL Server From non numeric datatype
 
SELECT     dbo.DEVICE.HOSTNAME, dbo.IPADDRESS.IPADDRESS,
CAST( ROUND( (dbo.IPADDRESS.IPADDRESS / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST( (ROUND( (dbo.IPADDRESS.IPADDRESS / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST( (ROUND( (dbo.IPADDRESS.IPADDRESS / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + 
CAST( (dbo.IPADDRESS.IPADDRESS % 256 ) AS varchar(4)) as IPDottedNotation
FROM dbo.IPADDRESS 

-- Oracle PL/SQL
SELECT DEVICE.HOSTNAME, IPADDRESS.IPADDRESS,
cast(
CAST(ROUND((IPADDRESS.IPADDRESS / 16777215 ), 0) AS varchar2(3)) || '.' ||
CAST(MOD(ROUND((IPADDRESS.IPADDRESS / 65535), 0), 256) AS varchar2(3)) || '.' ||
CAST(MOD(ROUND((IPADDRESS.IPADDRESS / 256  ), 0), 256) AS varchar2(3)) || '.' ||
CAST(MOD(IPADDRESS.IPADDRESS, 256 ) AS varchar2(3))
as char(20)) as IPDottedNotation
FROM IPADDRESS
 

conversions from unix epoch format OR loving the 1970s

Leave a comment

I recently encountered two conversion issues that are specific to working with computers. I address converting unix epoch date format to human readable date format here. My next post will address converting IP addresses stored as decimal integers to the more familiar dotted format.

There’s a popular work request management software product called Teamtrack from Serena software. The version deployed in my shop stores dates in its database using unix epoch format, which is the number of seconds since Jan 1, 1970. To convert this in a sql server t-sql query, I used:

select dateadd(s, table.date_field, '19700101') as friendlydate
from table

Interestingly, there were a few fields where I needed to do a slight modification, and I’m not sure why. Perhaps another timezone?

select dateadd(s, table.date_field,  '19691231 19:00:00') as friendlydate
from table

Forum poster jciappas from Brazil posts the Oracle PL/SQL version.

And at a dedicated web site for epoch converters, you can find code for doing the conversions in a variety of languages and databases. This web site covers going back and forth from unix epoch to human readable.