BigQuery - Listing partitions of the date partitioned table?

Question

Query -- SELECT partition_id from Mydataset.Table$__PARTITIONS_SUMMARY__; gives below error-- Table "Mydataset.Table$PARTITIONS_SUMMARY" cannot include decorator`

Answer 1

Try below in Legacy SQL

SELECT project_id,
    dataset_id,
    table_id,
    partition_id,
    TIMESTAMP(creation_time/1000) AS creation_time
FROM [Mydataset.Table$__PARTITIONS_SUMMARY__]

and below for Standard SQL

SELECT _PARTITIONTIME as pt
FROM `Mydataset.Table`
GROUP BY 1

which also works for Legacy SQL

SELECT _PARTITIONTIME as pt
FROM [Mydataset.Table]
GROUP BY 1

Answer 2

Old question but with a new answer

select partition_id
from `Mydataset.INFORMATION_SCHEMA.PARTITIONS`
where table_name = 'Table'

this is standard sql and your billing will be much less compared to using a group by