Range query on string fields having DateTime values in BigQuery

1
votes

I'm uploading data from csv file to bigquery table. Each record from csv contains datetime field values and i store those values into BigQuery table fields as string. When i query these datetime fields for the desired range(like dateField > '2013-01-01 00:00:00' and dateField < '2013-01-10 00:00:00'), i get wrong results.

I tried using TimeStamp functions but no luck. Please help me out.

1
google-bigquery

1 Answers

0
votes

I sorted out the problem:-

The time when i generate csv file with datetime(YYYY-MM-DD HH:MM:SS) values, I used a block of code using C# to convert this value into POSIX value(UNIX timestamp):-

        protected string convertDateTimeToPOSIX(string strDateTime)
        {
            string strPOSIX = "";
            DateTime dtmDateTime = DateTime.Parse(strDateTime);
            DateTime dtmEpoch = new DateTime(1970, 1, 1, 0, 0, 0, 0);
            TimeSpan objTSpan = (dtmDateTime - dtmEpoch);
            strPOSIX = objTSpan.TotalSeconds.ToString();
            return strPOSIX + "000000";
        }

So for eg. if strDateTime = "16-03-2011 23:51:17" then its POSIX value will become "1300319477000000"

Once the file is uploaded, I queried Big Query using web interface:-

    SELECT Col1, Col2, Col3, FORMAT_UTC_USEC(POSIX_DateTime) DATE FROM [Dataset.tableName] 
    WHERE POSIX_DateTime > PARSE_UTC_USEC('2013-01-01 00:00:00') AND POSIX_DateTime < PARSE_UTC_USEC('2013-01-11 00:00:00')
    ORDER BY DATE