Conversion Examples - Administration

First, let’s start with a C# sample code. We’re going to use its built-in DateTime function and its .AddSeconds method. The algorithm is simple: we ask the user to type in a UNIX time, then we parse to double, and right after, we create a new date that starts from 1970, 1, 1 (January 1) and we add the specified number of seconds to it. Ultimately, we can print this out, since it’s in the preformatted DateTime format.

Console.Write("Enter the source UNIX time: ");

double unixTime = double.Parse(Console.ReadLine());

DateTime convTime = new DateTime(1970, 1, 1).AddSeconds(unixTime);

Console.WriteLine("The converted time is: " + convTime);

And check out its output:

Enter the source UNIX time: 1206959497

The converted time is: 3/31/2008 10:31:37 AM

Press any key to continue . . .

Now let’s write the other way around. Here we work with the current time. We use the DateTime’s .Now property to extract the current time. Then we create a new double, which is going to represent the time in UNIX format. We can calculate its value if we somehow find out the offset between today’s date and 1970, 1, 1. We can do this with subtraction. Right after, we use the .TotalSeconds to build up the double.

DateTime currentTime = DateTime.Now;

Console.WriteLine("The current time now is: " + currentTime);

double currentUnixTime = (currentTime –

new DateTime(1970, 1, 1)).TotalSeconds;

Console.WriteLine("The current UNIX time now: " +

Math.Round(currentUnixTime,0));

Of course, here’s the output. Right at the time of writing this article, that is…

The current time now is: 3/31/2008 2:06:03 PM

The current UNIX time now: 1206972364

Press any key to continue . . .

Since working with UNIX timestamps is almost always necessary when working in UNIX or *NIX operating systems, I bookmarked this blog post (entry) right away when I found it. And I strongly suggest you do the same. It covers a few solutions for doing conversions in bash—perl and awk (scripting and programming languages).

Check out the following code snippets. The EPOCH variable is the source Unix time.

!#/bin/bash

EPOCH=1000000000

DATE=$(perl -e "require ‘ctime.pl’; print &ctime($EPOCH); ")

echo $DATE

DATE=$(perl -e "print scalar(localtime($EPOCH)) ")

echo $DATE

DATE=$(echo $EPOCH|awk ‘{print strftime(”%c”,$1)}’)

echo $DATE

DATE=$(awk “BEGIN { print strftime(”%c”,$EPOCH) }”)

echo $DATE

You could also use the following command under UNIX operating systems:

date -d "dategoeshere" +%s

As you can see from the example above, the syntax of Perl’s localtime is the following:

perl -e "print scalar localtime()"

Server-side programmers are familiar with situations where MySQL requires the timestamp in the Y-m-d H:i:sformat. And almost all of the techniques that “grab” the current time and date are returned in UNIX time format. The solution is conversion. And we can do this in PHP. Check out the attached code snippet below, where we’ve created two functions—conversion back and forth.

<?php

function unixToMySQL($unixtime)

{

return date('Y-m-d H:i:s', $timestamp);

}

… rest of the code goes here …

function convertToUnix($plaintime)

{

$plaintime = explode("/",$plaintime);

return mktime(0,0,0,$plaintime[1],$plaintime[2],plaintime[0]);

}

… rest of the code goes here …

?>

The algorithm is pretty straightforward. The date function does what we need when we want to convert from UNIX time format to any other“traditional” format. We just need to specify the requested format, such as Y-m-d H:i:s in this case. But this becomes more interesting when the situation is reversed and we need to split the string parameter into three parts (with the explode function at each slash “/”).

We use the mktime() function to make the time giving the necessary parameters. Please note that we’ve opted for 0, 0, and 0 as the first three parameters. This means that we’ve created a UNIX time at 00:00:00. We are purely focusing on splitting the date string and its conversion. Then we specify those parameters in terms of month, day, and year.

The following PHP solution is shorter, but does not behave reliably with DD/MM/YY:

# behaves erroneously with DD/MM/(YY)YY timestamps

strtotime($date);

Furthermore, I have also found this blog post where you can read about an ORACLE command that grabs the system time (SYSDATE) and converts it to a somewhat standard format (DD-MMM-YYYY). It’s based on the multiplication of 86400, but first we just calculate the offset from 01-JAN-1970.

SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) AS dt FROM dual;

Let me add the following MySQL example, too. Check out their syntax!

mysql> SELECT UNIX_TIMESTAMP('2008-03-31 16:34:14');

1206977654

UNIX_TIMESTAMP() —> Return a UNIX timestamp

FROM_UNIXTIME() —> Format date as a UNIX timestamp