Skip to main content

What is the default timestamp and how is it used?

What is the default timestamp and how is it used?

In this guide, you’ll learn how to select the optimal default timestamp for your table in Propel’s Serverless ClickHouse.

The default timestamp is an optional property of a Data Pool. It provides the time dimension to use for Metrics and more convenience to developers when querying over time ranges.

Pre-defined Metric example​

When defining a Metric, Propel will use the default timestamp column of the Data Pool as the time dimension for the Metric.

In the example below, we are creating a “Taco Revenue” Metric using the “TacoSoft Demo Data” Data Pool. The Metric will have the column timestamp as its time dimension because it is the Data Pool’s default timestamp.

A screenshot of the Propel Console showing a Metric with its Data Pool’s default timestamp. A screenshot of the Propel Console showing a Metric with its Data Pool’s default timestamp.

In-line Metric query example​

When querying with in-line Metrics (Metrics defined at query time), you don’t need to provide a time dimension when you’ve set a default timestamp in your Data Pool.

For instance, take the query below. Note that it doesn’t have a timestamp value in the time range. In this case, it will use the Data Pool’s default timestamp:

query TimeSeriesQuery {
  timeSeries(input: {
    metric: {
      count: {
        dataPool: {
          id: "TacoSoft Demo Data"
        }
      }
    },
    timeZone: "UTC",
    granularity: "DAY",
    timeRange: {
      relative: "LAST_N_DAYS",
      n: 30,
    },
    filters: []
  }) {
    labels
    values
  }
}

How to select a good default timestamp for your Data Pool​

To select a good default timestamp, consider two factors:

  1. The default timestamp should be the primary time dimension that you filter on.
  2. It should be part of the sorting key to ensure good query performance. For more information on the sorting key, see How to select a table engine and sorting key.

Querying with a different timestamp​

Even when you define a default timestamp for a Data Pool, you can still query it using a different timestamp by specifying it in the query.

The query below shows how to pass a different timestamp at query time:

query TimeSeriesQuery {
  timeSeries(input: {
    metric: {
      count: {
        dataPool: {
          id: "TacoSoft Demo Data"
        }
      }
    },
    timeZone: "UTC",
    granularity: "DAY",
    timeRange: {
      relative: "LAST_N_DAYS",
      n: 30,
      timestamp: "order_item_generated_at"
    },
    filters: []
  }) {
    labels
    values
  }
}

Further reading​