#Timseries

4 messages · Page 1 of 1 (latest)

molten mauve May 3, 2025, 12:44 AM

How can I store timeseries data in convex

foggy flickerBOT May 3, 2025, 12:44 AM

Hi @molten mauve. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

To store time series data in Convex, you have a few options depending on your specific needs:

Using Timestamps

The most straightforward approach is to store UTC timestamps as numbers (in milliseconds since epoch), similar to how Convex's built-in _creationTime field works:

Store the timestamp as a number field in your documents
Use Date.now() to get the current UTC timestamp in your functions
Create JavaScript Date objects when needed with new Date(timeInMsSinceEpoch)

As mentioned in the Convex documentation on working with dates and times, this approach works well when you only care about points in time.

Schema Example

Here's how you might define a schema for time series data:

// convex/schema.ts
import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

export default defineSchema({
  timeseriesData: defineTable({
    timestamp: v.number(), // milliseconds since epoch
    value: v.number(),     // your measurement
    deviceId: v.string(),  // or any other metadata you need
    // Add other fields as needed
  }).index("by_timestamp", ["timestamp"])
    .index("by_device_timestamp", ["deviceId", "timestamp"])
});

Querying Time Series Data

You can query your time series data using the indexes you've defined:

// Get data within a specific time range
export const getDataInTimeRange = query({
  args: { 
    startTime: v.number(),
    endTime: v.number(),
    deviceId: v.optional(v.string())
  },
  handler: async (ctx, args) => {
    const { startTime, endTime, deviceId } = args;
    
    let q = ctx.db.query("timeseriesData")
      .withIndex("by_timestamp")
      .gt("timestamp", startTime)
      .lt("timestamp", endTime);
      
    if (deviceId) {
      q = q.filter(q => q.eq(q.field("deviceId"), deviceId));
    }
    
    return await q.collect();
  },
});

Alternative: ISO8601 Strings

If you need to work with specific calendar dates or clock times (especially with timezone information), you might want to store dates as strings in ISO8601 format (e.g., "2024-03-21T14:37:15Z"). This is particularly useful for booking apps or when timezone information is important.

Considerations for Large Datasets

For very large time series datasets, you might want to consider:

Using a dedicated analytics tool alongside Convex by implementing streaming export to tools like BigQuery
Implementing time-based partitioning strategies (storing data in different tables based on time periods)

For analytics on your time series data, you can use Fivetran's Convex connector to replicate your data to external data warehouses that are optimized for analytical processing.

Remember that Convex is primarily an OLTP database optimized for transactional workloads rather than heavy analytical processing on large time series datasets.