Datetime trouble

Post here your questions about the Unity / .Net / Mono / Windows 8 / Windows Phone 8 API for SFS2X

Moderators: Lapo, Bax

User avatar
Scarpelius
Posts: 24
Joined: 12 Mar 2011, 11:33

Datetime trouble

Postby Scarpelius » 19 Aug 2011, 21:36

I have a problem with a datetime field retrieved from mySQL. The value in database is 2011-03-02 00:00:00 but when i display it in unity is missing exactly two hours and becomes 3/1/2011 10:00:00 PM

I am using this code to retrieve the data

Code: Select all

sql = " SELECT Name, Gender, DATE_FORMAT(CreationDate, '%W, %D %M %Y') as CreationDate FROM user WHERE id="+userId;
ISFSArray infoResult = dbManager.executeQuery(sql);
response.putSFSArray("userInfo", infoResult);


I am retrieving the value with

Code: Select all

string createDate = ConvertFromUnixTimestamp(Convert.ToDouble(userInfo.GetSFSObject(i).GetData("CreationDate").Data.ToString())).ToString();


where ConvertFromUnixTimestamp looks like this

Code: Select all

static DateTime ConvertFromUnixTimestamp(double timestamp)
    {
        Debug.Log(timestamp);
        DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
        return origin.AddMilliseconds(timestamp);
    }


Now I might be doing something wrong here, but i am unable to see the mistake at this hour. I don't know what to say I've checked the timestamps and difference is the same 7200 seconds (2 hours).

P.S. I am on RC2 haven't got the time to update the server, but is the first thing i am going to check tomorrow.
User avatar
rjgtav
Posts: 2813
Joined: 19 Apr 2009, 11:31
Location: Lisbon, Portugal

Postby rjgtav » 19 Aug 2011, 22:03

hi, sorry this is not my area, but i think the problem is client.side, specially the method you use for converting the data retrieved from the database to unity Date. I suggest you to google a simple tutorial about converting mysql date to unity date.
Skills: SFS Pro, SFS2X, AS2.0/AS3.0, Java, HTML5/CSS3/JS, C#
Portfolio: https://rjgtav.wordpress.com/
SFS Tutorials: http://sfs-tutor.blogspot.com/ - Discontinued. Some examples may be bugged.
User avatar
Scarpelius
Posts: 24
Joined: 12 Mar 2011, 11:33

Postby Scarpelius » 20 Aug 2011, 05:56

Believe me i tried :)
The timestamp value this gives userInfo.GetSFSObject(i).GetData("CreationDate").Data.ToString() is missing those 7200 seconds.

Btw userInfo is an ISFSArray.

UPDATE: same problem with RC3 :(
User avatar
rjgtav
Posts: 2813
Joined: 19 Apr 2009, 11:31
Location: Lisbon, Portugal

Postby rjgtav » 20 Aug 2011, 08:53

so you.re saying that the date retrieved from the server is different from the one in the database?
Skills: SFS Pro, SFS2X, AS2.0/AS3.0, Java, HTML5/CSS3/JS, C#
Portfolio: https://rjgtav.wordpress.com/
SFS Tutorials: http://sfs-tutor.blogspot.com/ - Discontinued. Some examples may be bugged.
User avatar
Scarpelius
Posts: 24
Joined: 12 Mar 2011, 11:33

Postby Scarpelius » 20 Aug 2011, 11:43

Yes.
User avatar
rjgtav
Posts: 2813
Joined: 19 Apr 2009, 11:31
Location: Lisbon, Portugal

Postby rjgtav » 20 Aug 2011, 14:26

O.O Strange. I can't confirm this as I dont use Unity, but can you try converting temporarily the Mysql column datatype to string and use it to store the date?
Skills: SFS Pro, SFS2X, AS2.0/AS3.0, Java, HTML5/CSS3/JS, C#
Portfolio: https://rjgtav.wordpress.com/
SFS Tutorials: http://sfs-tutor.blogspot.com/ - Discontinued. Some examples may be bugged.
User avatar
Scarpelius
Posts: 24
Joined: 12 Mar 2011, 11:33

Postby Scarpelius » 20 Aug 2011, 17:24

No, not really since the admin is done via web so i will mean to do heavy modifications there too for every column that is datetime and behave this way.

The safest hack is to extract the data from db as string with CONVERT(mydata USING latin1) as stringData but is not a solution.
User avatar
Scarpelius
Posts: 24
Joined: 12 Mar 2011, 11:33

Postby Scarpelius » 20 Aug 2011, 18:37

Ok, I think found an explication for this, it has something to do with the fact my server and my client both run on GMT+2

For some reasons, smartfox converts values like this 2011-03-02 00:00:00 into timestamp but there is a trap too here. The value returned is not the time in seconds since January 1 1970 is the time in milliseconds.
The problem is the fact that this conversion probably takes into consideration the time shift for server and add/substract that value from result, hence my 2 hours difference.

Anyway i have to read more about the way unix timestamp is working.
eldoraman
Posts: 1
Joined: 26 Apr 2017, 05:18

Re: Datetime trouble

Postby eldoraman » 26 Apr 2017, 05:19

Try this one....Timestamp to date

Eldo
ReaksmeyRT
Posts: 4
Joined: 20 Nov 2019, 10:31

Re: Datetime trouble

Postby ReaksmeyRT » 16 Dec 2020, 10:14

Hi Dear,
I had the same problem ,and I try this at client side in unity c#:

public string ConvertToTimestamp(long unixTime)
{
DateTime unixStart = new DateTime(1970, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc).ToLocalTime() ;// hour local zone
long unixTimeStampInTicks = (unixTime * TimeSpan.TicksPerMillisecond);
string date = new DateTime(unixStart.Ticks + unixTimeStampInTicks, System.DateTimeKind.Utc).ToString();
return date;
}

Return to “SFS2X C# API”

Who is online

Users browsing this forum: No registered users and 21 guests