c# mysql datetime to unix time

Question

I have the following problem. The timeformat my database gives me back depends on the server settings. So, if I do this query:

string query = "SELECT d.id, i.name, d.address, d.sensor, d.value, d.made, d.received, s.type, r.name AS room
            FROM info AS i
            RIGHT JOIN data AS d ON i.address = d.address AND i.sensor = d.sensor
            LEFT  JOIN sensor_types AS s ON i.type = s.id
            LEFT  JOIN room AS r ON i.room = r.id
            WHERE i.address = '" + address + "'
            && i.sensor  = '" + sensor  + "'
            && DATE(d.received) BETWEEN '" + start + "' AND '" + end + "'";

On server 1 I get back for today 2013-12-23 2:29:14 and on server 2 I get back 23-12-2013 2:29:14.

In some cases I need the unixtime so I use this function for it, which works fine in case 1 but gives an error in case 2:

public string DateTimeToUnixTime(string text)
{
    DateTime myDate = DateTime.ParseExact(text, "yyyy-MM-dd HH:mm:ss",
                               System.Globalization.CultureInfo.InvariantCulture);
    TimeSpan span = (myDate - new DateTime(1970, 1, 1, 0, 0, 0, 0).ToLocalTime());

    //return the total seconds (which is a UNIX timestamp)
    return ((double)span.TotalSeconds).ToString();

}

What is the best way to fix this?

jomsk1e · Accepted Answer · 2013-12-23 06:43:41Z

2

If you need to convert datetime to unix time. You can use UNIX_TIMESTAMP() function of MySQL for that.

Try this:

SELECT UNIX_TIMESTAMP(STR_TO_DATE('Dec 23 2013 02:29AM', '%M %d %Y %h:%i%p'))

You can read more here.

Hope that helps! :)

answered Dec 23, 2013 at 6:43

jomsk1e

3,6457 gold badges37 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grant Winney · Accepted Answer · 2013-12-23 01:41:43Z

2

Not saying it's the best way, but if the only possible formats are those two, you could test for one using DateTime.TryParseExact, and if it fails, assume it's the other format.

DateTime myDate;

if (!DateTime.TryParseExact(text, "yyyy-MM-dd HH:mm:ss",
                           System.Globalization.CultureInfo.InvariantCulture,
                           DateTimeStyles.None, out myDate);
{
    myDate = DateTime.ParseExact(text, "dd-MM-yyyy HH:mm:ss",
                           System.Globalization.CultureInfo.InvariantCulture);
}

answered Dec 23, 2013 at 1:41

Grant Winney

66.6k13 gold badges120 silver badges167 bronze badges

Collectives™ on Stack Overflow

c# mysql datetime to unix time

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related