Let's further discuss these data types:
- Floating-point: Unlike integers, which must be whole numbers, floating-point numbers can have fractional components and can have special non-numeric values such as NaN (not a number), Inf (infinite), or null.
In order to avoid non-numeric values, make sure your mathematical calculations deal with non-numerics using the IFNULL() and <IFNULL(FUNCTION,0)> function for Legacy SQL. For Standard SQL, wrap the IFNULL() function around the SAFE_DIVIDE() function for situations such as dividing zero by zero.
- String: Don't store all values as string in the table. Use appropriate data types, for example for TRUE or FALSE use Boolean type instead of storing the value as string.
- Date: Date is stored in YYYY-MM-DD format. Month and day can both be either 2 or 1 digit.
- Datetime: Usually used to mark events such as transactions. Datetime is stored in YYYY-MM-DD HH:MM:SS.DDDDDD format. Month, day, hour, and second can be 2 or 1 digit. Fractional digits can range from 1-6 digits and are optional.
- Time: Stored in HH:MM:SS.DDDDDD format. Hour and second can be 2 or 1 digit. Fractional digits can range from 1-6 digits and are optional.
- Timestamp: Used to mark an absolute point in time with microsecond precision. Month, Day, Hour, and Second can be 2 or 1 digit. Fractional digits can range from 1-6 digits and are optional. Time zones and Daylight Saving time can be expressed in Timestamps. This is stored in YYYY-MM-DD HH:MM:SS.DDDDDDTZ.
Time zones can be expressed in Timestamps using Coordinated Universal Time (UTC) and an offset. For instance, United States Central Standard Time would be expressed like this YYYY-MM-DD HH:MM:SS-6:00.
- Array: Arrays can be used to pass and ordered lists of zero or more values of the same data type.
Arrays cannot contain arrays, but the STRUCT type can be used as a container for arrays.