I'm working with an old database and need to start generating the id using c#. I need to generate id's that correspond with the old id's.
I'd like to convert a DateTime to a 7 digit \ base36 configuration. I thought it would be easy enough to reverse engineer once I had the code to convert from base36 code to DateTime (Thanks again Joshua), however I'm still having difficulties.
I've spent the day trying to figure out how to convert from DateTime to base36.
Below is the code to convert from base36 code to DateTime. This code seems to work fine. The id is added to sRecid and it is then converted to DateTime.
id Date Time
A7LXZMM 2004-02-02 09:34:47.000
KWZKXEX 2018-11-09 11:15:46.000
LIZTMR9 2019-09-13 11:49:46.000
using System;
using System.Globalization;
using System.Text;
using System.Numerics;
public class Program
{
public static void Main()
{
string sRecid = "KWZKXEX";
char c0 = sRecid[0];
char c1 = sRecid[1];
char c2 = sRecid[2];
char c3 = sRecid[3];
char c4 = sRecid[4];
char c5 = sRecid[5];
char c6 = sRecid[6];
double d6, d5, d4, d3, d2, d1, d0, dsecs;
Console.WriteLine("c0 = " + c0.ToString());
Console.WriteLine();
d6 = Math.Pow(36, 6) * ((Char.IsNumber(c0)) ? (byte)c0 - 48 : (byte)c0 - 55);
d5 = Math.Pow(36, 5) * ((Char.IsNumber(c1)) ? (byte)c1 - 48 : (byte)c1 - 55);
d4 = Math.Pow(36, 4) * ((Char.IsNumber(c2)) ? (byte)c2 - 48 : (byte)c2 - 55);
d3 = Math.Pow(36, 3) * ((Char.IsNumber(c3)) ? (byte)c3 - 48 : (byte)c3 - 55);
d2 = Math.Pow(36, 2) * ((Char.IsNumber(c4)) ? (byte)c4 - 48 : (byte)c4 - 55);
d1 = Math.Pow(36, 1) * ((Char.IsNumber(c5)) ? (byte)c5 - 48 : (byte)c5 - 55);
d0 = Math.Pow(36, 0) * ((Char.IsNumber(c6)) ? (byte)c6 - 48 : (byte)c6 - 55);
dsecs = (d6 + d5 + d4 + d3 + d2 + d1 + d0) / 50;
DateTime dt = new DateTime(1990, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc);
dt = dt.AddSeconds(dsecs).ToLocalTime();
Console.WriteLine("d6 = " + d6.ToString());
Console.WriteLine("d5 = " + d5.ToString());
Console.WriteLine("d4 = " + d4.ToString());
Console.WriteLine("d3 = " + d3.ToString());
Console.WriteLine("d2 = " + d2.ToString());
Console.WriteLine("d1 = " + d1.ToString());
Console.WriteLine("d0 = " + d0.ToString());
Console.WriteLine("dsecs = " + dsecs.ToString());
Console.WriteLine("dt = " + dt.ToString());
}
}
This is the code I'm having problems with.
using System;
using System.Globalization;
using System.Text;
using System.Numerics;
public class Program
{
/*
A7LXZMM 2004-02-02 09:34:47.000
KWZKXEX 2018-11-09 11:15:46.000
LIZTMR9 2019-09-13 11:49:46.000
*/
public static void Main()
{
DateTime dt = new DateTime(2004, 02, 02, 09, 34, 47); // Convert this datetime to A7LXZMM
DateTime dtBase = new DateTime(1990, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc);
double offsetseconds = (DateTime.Now - DateTime.UtcNow).TotalSeconds;
double seconds = ((dt - dtBase).TotalSeconds) * 50;
double d6 = seconds / (Math.Pow(36, 6));
var q6 = d6.ToString().Split('.');
double dQuotient = double.Parse(q6[0]);
double dRemainder = double.Parse(q6[1]);
char c0 = ((dQuotient <= 9) ? (char)(dQuotient + 48) : (char)(dQuotient + 55));
Console.WriteLine("d6 = " + d6.ToString());
Console.WriteLine("dQuotient = " + dQuotient.ToString());
Console.WriteLine("c0 = " + c0.ToString());
Console.WriteLine("");
double d5 = dQuotient / (Math.Pow(36, 5));
var q5 = d5.ToString().Split('.');
dQuotient = double.Parse(q5[0]);
dRemainder = double.Parse(q5[1]);
char c1 = ((dQuotient <= 9) ? (char)(dQuotient + 48) : (char)(dQuotient + 55));
Console.WriteLine("d5 = " + d5.ToString());
Console.WriteLine("dQuotient = " + dQuotient.ToString());
Console.WriteLine("c1 = " + c1.ToString());
Console.WriteLine("");
}
}
The code starts off fine, I'm able to get the first char (c0), but I'm having trouble with the next chars (c1 onwards).
In my example, I'm passing in the date 2004-02-02 09:34:47 and am intending on getting back A7LXZMM. Where am I going wrong?
Thanks.
The root of your problem is your failure to incorporate your local time zone offset in the calculation. You make an attempt to determine the TZ offset, albeit in an incorrect fashion, but that's never actually used.
The reason that attempt is incorrect is that you retrieve the current time twice, and the current time could in fact change between those two calls. In the .NET environment it's unlikely, due to the relatively low resolution of the DateTime.Now and .UtcNow properties, but it can in theory happen if you catch the calls at just the right moment.
Of course, that bug doesn't matter, because the computed value is never used.
IMHO, the other big thing wrong with both code examples is that they are insufficiently generalized, and insufficiently abstracted:
One final big thing wrong with the code is that it adjusts the encoded time to local time. Local time is for human consumption only. This ties in with the abstraction aspect to some extent. But the main point is that when dealing with time values, your code should only ever use UTC internally. The local time is useful only when interacting with the user, and even then only to support user scenarios where you want the user to specifically work in their local time zone (sometimes you have users all over the world and they are coordinating with each other with your time values, and it makes sense to force the users into using UTC).
That last point also makes it hard for the rest of us to involve ourselves with your code, because you are in a different time zone from the rest of us (one hour ahead of UTC, it appears).
Here is an example of how I would approach the problem. The main thing here is that I've broken the Base36 handling out as separate from the database value handling. I've also introduced an explicit time zone offset parameter and switched to using DateTimeOffset instead of DateTime, because that allows for me to work in your time zone. :) In reality, I would use DateTimeOffset values, but only use UTC, i.e. with an offset of 0. The non-zero offset is for the sake of this example only, and the offset parameters could be omitted in production code if you stick with UTC internally.
First, a Base36 encoder class:
static class Base36
{
public static string EncodeAsFixedWidth(long value, int totalWidth)
{
string base36Text = Encode(value);
return base36Text.PadLeft(totalWidth, '0');
}
public static string Encode(long value)
{
StringBuilder sb = new StringBuilder();
while (value >= 36)
{
int digit = (int)(value % 36);
char digitCharacter = _GetDigitCharacter(digit);
sb.Append(digitCharacter);
value = value / 36;
}
sb.Append(_GetDigitCharacter((int)value));
_Reverse(sb);
return sb.ToString();
}
public static long Decode(string base36Text)
{
long value = 0;
foreach (char ch in base36Text)
{
value = value * 36 + _GetBase36DigitValue(ch);
}
return value;
}
private static void _Reverse(StringBuilder sb)
{
for (int i = 0; i < sb.Length / 2; i++)
{
char ch = sb[i];
sb[i] = sb[sb.Length - i - 1];
sb[sb.Length - i - 1] = ch;
}
}
private static int _GetBase36DigitValue(char ch)
{
return ch < 'A' ? ch - '0' : ch - 'A' + 10;
}
private static char _GetDigitCharacter(int digit)
{
return (char)(digit < 10 ? '0' + digit : 'A' + digit - 10);
}
}
Okay, now that we have that, it's easy to write a class to handle the encoding for the database value itself:
static class DatabaseDateTime
{
private static readonly DateTimeOffset _epoch = new DateTimeOffset(1990, 1, 1, 0, 0, 0, 0, TimeSpan.Zero);
public static DateTimeOffset Decode(string databaseText, int timeZoneOffsetHours)
{
double secondsSinceEpoch = Base36.Decode(databaseText) / 50d;
DateTimeOffset result = _epoch.AddSeconds(secondsSinceEpoch);
return new DateTimeOffset(result.AddHours(timeZoneOffsetHours).Ticks, TimeSpan.FromHours(timeZoneOffsetHours));
}
internal static string Encode(DateTimeOffset testResult)
{
double secondsSinceEpoch = testResult.Subtract(_epoch).TotalSeconds;
return Base36.EncodeAsFixedWidth((long)(secondsSinceEpoch * 50), 7);
}
}
Finally, for the purpose of this exercise, I wrote a little sample program that uses your example values to verify that the above code works as expected and desired:
static void Main(string[] args)
{
(string DatabaseText, DateTimeOffset EncodedDateTime)[] testCases =
{
("A7LXZMM", DateTimeOffset.Parse("2004-02-02 09:34:47.000 +01:00")),
("KWZKXEX", DateTimeOffset.Parse("2018-11-09 11:15:46.000 +01:00")),
("LIZTMR9", DateTimeOffset.Parse("2019-09-13 11:49:46.000 +01:00"))
};
List<DateTimeOffset> testResults = new List<DateTimeOffset>(testCases.Length);
foreach (var testCase in testCases)
{
DateTimeOffset decodedDateTime = DatabaseDateTime.Decode(testCase.DatabaseText, 1);
// Compare as string, because reference data was provided as string and is missing
// some of the precision in the actual database text provided.
if (decodedDateTime.ToString() != testCase.EncodedDateTime.ToString())
{
WriteLine($"ERROR: {testCase.DatabaseText} -- expected: {testCase.EncodedDateTime}, actual: {decodedDateTime}");
}
testResults.Add(decodedDateTime);
}
foreach (var testCase in testResults.Zip(testCases, (r, c) => (Result: r, DatabaseText: c.DatabaseText)))
{
string base36Text = DatabaseDateTime.Encode(testCase.Result);
if (base36Text != testCase.DatabaseText)
{
WriteLine($"ERROR: {testCase.Result} -- expected: {testCase.DatabaseText}, actual: {base36Text}");
}
}
}
When I run the above code, I get no output, just as desired (i.e. the only WriteLine() calls above are executed only when the program's computations don't match what's expected).