CODEWITHSUNDEEP
sqlsql-server
Alan
Alan

Reputation: 125

How to convert this time format from SQL Server database

So we have a software vendor that has time stored in a database in a CHAR(216) field. In the GUI you can select open hours for a door in increments of 15 minutes. So in my example below the hours would be open Mon-Fri from 8 AM to 5 PM. So the fun part. Here is how the time bits are stored in SQL for my example below. From what I can tell the data is stored in order from Sunday-Saturday. Does anyone have any ideas on how I would be able to convert this into the actual days and hours?

Mon-Fri 8AM-5PM: 00000000000000000000000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F000000000000000000000000000000000000000000000000000000000000000000000000000000

All Closed: 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

All Open: FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF

Open Hours

Upvotes: 0

Views: 115

Answers (1)

JimmyB
JimmyB

Reputation: 12610

Calculate the index i of every 1 bit in the stream. i*15 is the number of minutes since the start of the week (Sunday, 00:00?), so you add that number of minutes to the start of the week and get the point in time where that opening interval begins.

Apparently, the data is represented byte-wise with the LSB first. Note that each hex character represents 4 bits, i.e. exactly one hour.

You could iterate over the string, extracting two hex characters at a time, converting to an integer and then finding the bits set while incrementing your index i for every bit processed.

Pseudo code:

byte currentByte;
for ( int bitIndex = 0; bitIndex < (216*4); bitIndex++ ) {
  if ( bitIndex % 8 == 0 ) {
    /* Need to fetch next byte from the bit-map */
    int stringIndex = bitIndex / 4; /* 4 bits make up one character */
    String hexByte = substring( input, stringIndex, stringIndex + 2); /* get next two hex characters */
    currentByte = parse_hex( hexByte );
  }
  if ( (currentByte & 1) != 0 ) {
    /* Lowest bit is set -> open! */
    output( "Open starting at " + (bitIndex * 15) + " minutes from the start of the week.");
  } else {
    /* Lowest bit not set -> closed! */
  }
  currentByte = currentByte / 2; /* logical shift right by 1 bit */
}

Upvotes: 2

Related Questions