Major Description
Date time functions have inconsistent behavior when called on views vs normal collections.
Following are some examples.
Examples with views
select date_part_str(review_timestamp, 'day') as day from `travel-sample`.`inventory`.hotel_review_view limit 1; |
|
|
[
|
{
|
"day": null |
}
|
]
|
With views an explicit cast to string is required for date_part_str function.
select date_part_str(to_string(review_timestamp), 'day') as day from `travel-sample`.`inventory`.hotel_review_view limit 1; |
|
|
[
|
{
|
"day": 22 |
}
|
]
|
On the collection on top of which the view is created the date_part functions does not seem to work
select date_part_str(to_string(r.date), 'day') as day from `travel-sample`.inventory.hotel h unnest h.reviews r limit 1 |
[
|
{
|
"day": null |
}
|
]
|
|
|
select date_part_str(r.date, 'day') as day from `travel-sample`.inventory.hotel h unnest h.reviews r limit 1 |
[
|
{
|
"day": null |
}
|
]
|
|
With another dataset
-- on checkinsRaw collection
|
select date_part_str(date, 'day') from `test-data`.checkinsRaw limit 1 |
|
|
[
|
{
|
"$1": 7 |
}
|
]
|
|
|
-- on checkins view created on checkinsRaw collection
|
select date_part_str(date, 'day') from `test-data`.checkins limit 1 |
|
|
[
|
{
|
"$1": null |
}
|
]
|
|
|
select date_part_str(to_string(date), 'day') from `test-data`.checkins limit 1 |
[
|
{
|
"$1": 7 |
}
|
]
|
|
Following are 5 rows from the checkinsRaw collection
select * from `test-data`.checkinsRaw limit 5 |
[
|
{
|
"checkinsRaw": { |
"date": "2014-04-07", |
"id": 1, |
"user_id": 5, |
"venue_id": 12 |
}
|
},
|
{
|
"checkinsRaw": { |
"date": "2014-09-18", |
"id": 2, |
"user_id": 1, |
"venue_id": 31 |
}
|
},
|
{
|
"checkinsRaw": { |
"date": "2013-03-25", |
"id": 11, |
"user_id": 15, |
"venue_id": 23 |
}
|
},
|
{
|
"checkinsRaw": { |
"date": "2015-09-04", |
"id": 101, |
"user_id": 11, |
"venue_id": 99 |
}
|
},
|
{
|
"checkinsRaw": { |
"date": "2013-12-02", |
"id": 102, |
"user_id": 5, |
"venue_id": 44 |
}
|
}
|
]
|
Following is the definition of checkins view
create or replace analytics view `test-data`.checkins(id BIGINT, date DATE, user_id BIGINT, venue_id BIGINT
|
) as `test-data`.checkinsRaw
|