Unix datetime confusion

[Solarion]

I am wanting to save a dateofbirth into an SQL table as an integer. Then when retrieving the dateofbirth from SQL I will convert this integer back to the date of birth.

I have literally inputted the value into the table for now which is 1072540000 so actually converting back to a date time is the easy part. However I would like to save the date back into the table as it's unix value but there is a mismatch: System.InvalidCastException: 'Invalid cast from 'DateTime' to 'Int32'.'

How the heck do I achieve this when my DateofBirth property is a DateTime?

 

[etienne_marais]

I think you should try using long instead:

long unixTimeStamp = ((DateTimeOffset)youDateTimeVariable).ToUnixTimeSeconds() (.NET 4.6+)

According to: https://stackoverflow.com/questions/17632584/how-to-get-the-unix-timestamp-in-c-sharp
you should be mindful of the timezone as you are using DateTimeOffset.

 

[etienne_marais]

Also note from the same site you can use this:

int32 unixTimestamp = (Int32)(YourDateTimeVariable.Subtract(new DateTime(1970, 1, 1))).TotalSeconds;

This goes hand in hand with the definition of a unix timestamp which is the number of seconds since 1970

 

[Solarion]

I think you should try using long instead:

long unixTimeStamp = ((DateTimeOffset)youDateTimeVariable).ToUnixTimeSeconds() (.NET 4.6+)

According to: https://stackoverflow.com/questions/17632584/how-to-get-the-unix-timestamp-in-c-sharp
you should be mindful of the timezone as you are using DateTimeOffset.

Thanks etienne, I found my answer in there. I usually only ask here when all out of options so I did look far and wide, just didn't come across that thread!

 

[Solarion]

Shot bru big help

 

[Solarion]

I've come unstuck again, this time when pulling data from SQL

StudentPicture = (byte[])(record["StudentPicture"])

Getting the error:

"Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'."

Any ideas? I know it's trying to unbox directly into a bye[] and I usually deal with unboxing by explicitly converting to the relevant type, but I have no idea how to do that here!

 

[etienne_marais]

Did not test, but something like

StudentPicture = (record["StudentPicture]==DBNull.Value)?null : ( byte[])record["StudentPicture"]

Or else don't allow nulls in db, let default be empty string or such

 

[Hamster]

^ DBNull = null, as in there's no data in the db. Do a check before you cast?

My real reason for posting: why are you persisting date values as numbers and not, well, dates?

 

[Spacerat]

My real reason for posting: why are you persisting date values as numbers and not, well, dates?

This.... most DB platforms support date as a native supported type

 

[etienne_marais]

I'm guessing it has to do with his source, perhaps he is not importing with c# processing or easy date conversion available, let's see what he says

 

[Solarion]

StudentPicture = (record["StudentPicture]==DBNull.Value)?null : ( byte[])record["StudentPicture"]

StudentPicture = (record["StudentPicture"] == DBNull.Value) ?null : (byte[])record["StudentPicture"]

And with that you saved my sanity and I can now take the rest of the night off (self imposed checkpoint )

Thank-you once again Etienne.

^ DBNull = null, as in there's no data in the db. Do a check before you cast?

My real reason for posting: why are you persisting date values as numbers and not, well, dates?

Has something to do with localization and date of births being calculated differently in certain countries. I kind of latched onto it as it seemed quite a precise way of storing a birth date.

 

[Hamster]

Has something to do with localization and date of births being calculated differently in certain countries. I kind of latched onto it as it seemed quite a precise way of storing a birth date.

I suppose it's a hobby project so you can do what you want, but in a place of work I would tell you to stop being weird and use UTC.

 

[Johnatan56]

StudentPicture = (record["StudentPicture"] == DBNull.Value) ?null : (byte[])record["StudentPicture"]

And with that you saved my sanity and I can now take the rest of the night off (self imposed checkpoint )

Thank-you once again Etienne.



Has something to do with localization and date of births being calculated differently in certain countries. I kind of latched onto it as it seemed quite a precise way of storing a birth date.

I hope this system is not expected to last past 19 Jan 2038...

 

[etienne_marais]

I hope this system is not expected to last past 19 Jan 2038...

Solarion, note that there is a DATETIMEOFFSET which is the date and time and offset from UTC in the same column for some time in SQL Server now, alongside some manipulation functions.

Either way, you also need to incorporate daylight savings time for a global application. It gets tricky if a user enters a date on the day before DLS adjustment happens and then say fly to another time zone alltogether in that you have to decide if all happens server side or perhaps on the client app etc. The SQL 70-460 exam tutorials on youtube deals with this in depth.

 

[Hamster]

I hope this system is not expected to last past 19 Jan 2038...

I was going to ask him that and pre 1901 but then I realised the cast exception is what started all of this

 

[Johnatan56]

I was going to ask him that and pre 1901 but then I realised the cast exception is what started all of this

Well I'd have expected that he already hit the 1901 issue by now as a test case, but people always forget about testing future values.