Big Query (SQL): Date Problems or Situations that are Hard to Analyze and Takes Time To Crack

   Being a Business Analyst in a famous E-commerce in the Philippines, we used Google Big Query to extract some data from an E-commerce platform. Big Query is a Structured Query Language that manipulates raw data in a summarized and desired information.

    As a beginner user,  it is very hard to understand since it was not taught in college. However, I am privileged and happy because Big Query is owned by Google Company, hence, it is very expensive, so, this is once in a lifetime opportunity to grab on and a ladder to your career.

    Though it is relatively difficult for me, I want to share with you two situations or problems that may you have encountered in Big Query. In the meantime, I will give only a broader hint in analyzing the problem but later in the next post, I will show you my way to solve my problem. 

Getting a Monthly Date Range

    Big Query has a lot of Date Functions. These are the following:

• CURRENT_DATE
• EXTRACT
• DATE
• DATE_ADD
• DATE_SUB
• DATE_DIFF
• DATE_TRUNC
• DATE_FROM_UNIX_DATE
• FORMAT_DATE
• LAST_DAY
• PARSE_DATE
• UNIX_DATE

For more information in each date functions, read it here.

     This is the problem I have encountered. Given those dates below, you should make a date range. A date range starts from 29th of the month and ends with 28th of the next month.  Assuming the table name is Table_Range.

    The result should be like this. Hint: Use some of those date functions that are listed above.

Delivery_Due_Date	Starting Date	Ending Date
03-13-2020	02-29-2020	03-28-2021
06-30-2020	06-29-2020	07-28-2021
01-01-2021	12-29-2020	01-28-2021
11-11-2021	10-28-2021	11-29-2021

I also asked the Stackoverflow Community page and they have also their suggested answer. You can also check my suggested query here.

 

A Decimal String Style of Datestamp from an Airtable to a Correct and True DateStamp in a Big Query.

    Airtable is an online database tool that organizes projects, collabs data, and manages project schedules. The archives that are produced in this online tool can be uploaded as raw data in Big Query. 

    In Airtable the DateTime Data is correct but if you upload it in the Big Query, it will transform into a Decimal String Style of Numbers.  See the table below:

Delivery_Due_Date	True Date
44493.61666667	2021-10-24T14:48:00
44522.775	2021-11-22T18:36:00
44493.67916666667	2021-10-24T16:18:00
44522.739583333336	2021-11-22T17:45:00

    This is the problem I have encountered. Assuming the table name is Airtable.  Can you give a suggested query to convert it into the correct and true timestamp? Hint: I used the split function and offset function to solve the second column.

I also asked the Stackoverflow Community page and they have also their suggested answer. You can also check my suggested query here.

 

    As of now, these are the problems that I have encountered in my career.

  If you want to share your suggested ways to solve it, I am very lovely and thankful to see and read on it. Just comment on this post for your suggested queries.