Press n or j to go to the next uncovered block, b, p or k for the previous block.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 | 3160x 1196x 1196x 40x 1156x 515x 515x 244x 244x 244x 236x 196x 196x 196x 196x 48x 148x 236x 271x 263x 263x 223x 55x 55x 55x 168x 168x 223x 40x 40x 24x 263x 263x 263x 263x 263x 6x 6x 6x 18x 18x 24x 8x 271x 271x 271x 271x 271x 271x 271x 12x 12x 12x 12x 12x 259x 8x 8x 8x 8x 8x 8x 8x 251x 8x 8x 8x 8x 8x 8x 8x 8x 243x 20x 20x 20x 223x 8x 8x 8x 8x 215x 16x 16x 16x 199x 199x 56x 56x 56x 56x 56x 56x 56x 143x 143x 4x 4x 4x 4x 4x 4x 4x 4x 139x 8x 8x 8x 8x 8x 8x 8x 8x 131x 8x 8x 8x 123x 12x 12x 12x 12x 12x 12x 111x 27x 27x 27x 84x 20x 20x 20x 20x 64x 64x 12x 12x 12x 12x 12x 52x 52x 12x 12x 12x 12x 12x 40x 990x 990x 990x 990x 990x 782x 782x 766x 185x 581x 208x 208x 128x 30x 98x | /**
* Date/Time Builder
* Handles all date and time related SQL generation:
* - Relative date range parsing (today, last 7 days, etc.)
* - Date normalization across database engines
* - Date range condition building
* - Time dimension expression building with granularity
*/
import {
sql,
and,
gte,
lte,
SQL,
type AnyColumn
} from 'drizzle-orm'
import type { TimeGranularity, QueryContext } from '../types'
import { resolveSqlExpression } from '../cube-utils'
import type { DatabaseAdapter } from '../adapters/base-adapter'
export class DateTimeBuilder {
constructor(private databaseAdapter: DatabaseAdapter) {}
/**
* Build time dimension expression with granularity using database adapter
*/
buildTimeDimensionExpression(
dimensionSql: any,
granularity: string | undefined,
context: QueryContext
): SQL {
const baseExpr = resolveSqlExpression(dimensionSql, context)
if (!granularity) {
// Ensure we return SQL even when no granularity is applied
return baseExpr instanceof SQL ? baseExpr : sql`${baseExpr}`
}
// Use database adapter for database-specific time dimension building
return this.databaseAdapter.buildTimeDimension(granularity as TimeGranularity, baseExpr)
}
/**
* Build date range condition for time dimensions
*/
buildDateRangeCondition(
fieldExpr: AnyColumn | SQL,
dateRange: string | string[]
): SQL | null {
Iif (!dateRange) return null
// Handle array date range first
if (Array.isArray(dateRange) && dateRange.length >= 2) {
const startDate = this.normalizeDate(dateRange[0])
let endDate = this.normalizeDate(dateRange[1])
if (!startDate || !endDate) return null
// For date-only strings, treat end date as end-of-day (23:59:59.999)
// to include all records on that day
if (typeof dateRange[1] === 'string' && /^\d{4}-\d{2}-\d{2}$/.test(dateRange[1].trim())) {
const endDateObj = typeof endDate === 'number'
? new Date(endDate * (this.databaseAdapter.getEngineType() === 'sqlite' ? 1000 : 1))
: new Date(endDate)
const endOfDay = new Date(endDateObj)
endOfDay.setUTCHours(23, 59, 59, 999)
if (this.databaseAdapter.isTimestampInteger()) {
endDate = this.databaseAdapter.getEngineType() === 'sqlite'
? Math.floor(endOfDay.getTime() / 1000)
: endOfDay.getTime()
} else {
// PostgreSQL and MySQL need ISO strings
endDate = endOfDay.toISOString()
}
}
return and(
gte(fieldExpr as AnyColumn, startDate),
lte(fieldExpr as AnyColumn, endDate)
) as SQL
}
// Handle string date range
if (typeof dateRange === 'string') {
// Handle relative date expressions
const relativeDates = this.parseRelativeDateRange(dateRange)
if (relativeDates) {
// Convert Date objects to appropriate format for the database
let start: string | number
let end: string | number
if (this.databaseAdapter.isTimestampInteger()) {
if (this.databaseAdapter.getEngineType() === 'sqlite') {
start = Math.floor(relativeDates.start.getTime() / 1000)
end = Math.floor(relativeDates.end.getTime() / 1000)
} else E{
start = relativeDates.start.getTime()
end = relativeDates.end.getTime()
}
} else {
// PostgreSQL and MySQL need ISO strings
start = relativeDates.start.toISOString()
end = relativeDates.end.toISOString()
}
return and(
gte(fieldExpr as AnyColumn, start),
lte(fieldExpr as AnyColumn, end)
) as SQL
}
// Handle absolute date (single date)
const normalizedDate = this.normalizeDate(dateRange)
if (!normalizedDate) return null
// For single date, create range for the whole day
// normalizedDate might be a timestamp (number) or ISO string depending on database
const dateObj = typeof normalizedDate === 'number'
? new Date(normalizedDate * (this.databaseAdapter.getEngineType() === 'sqlite' ? 1000 : 1))
: new Date(normalizedDate)
const startOfDay = new Date(dateObj)
startOfDay.setUTCHours(0, 0, 0, 0) // Ensure we start at midnight UTC
const endOfDay = new Date(dateObj)
endOfDay.setUTCHours(23, 59, 59, 999) // Ensure we end at 11:59:59.999 UTC
// Convert to appropriate format for the database
let startValue: string | number
let endValue: string | number
if (this.databaseAdapter.isTimestampInteger()) {
if (this.databaseAdapter.getEngineType() === 'sqlite') {
startValue = Math.floor(startOfDay.getTime() / 1000)
endValue = Math.floor(endOfDay.getTime() / 1000)
} else E{
startValue = startOfDay.getTime()
endValue = endOfDay.getTime()
}
} else {
// PostgreSQL and MySQL need ISO strings
startValue = startOfDay.toISOString()
endValue = endOfDay.toISOString()
}
return and(
gte(fieldExpr as AnyColumn, startValue),
lte(fieldExpr as AnyColumn, endValue)
) as SQL
}
return null
}
/**
* Parse relative date range expressions like "today", "yesterday", "last 7 days", "this month", etc.
* Handles all 14 DATE_RANGE_OPTIONS from the client
*/
parseRelativeDateRange(dateRange: string): { start: Date; end: Date } | null {
const now = new Date()
const lowerRange = dateRange.toLowerCase().trim()
// Extract UTC date components for consistent calculations
const utcYear = now.getUTCFullYear()
const utcMonth = now.getUTCMonth()
const utcDate = now.getUTCDate()
const utcDay = now.getUTCDay()
// Handle "today"
if (lowerRange === 'today') {
const start = new Date(now)
start.setUTCHours(0, 0, 0, 0)
const end = new Date(now)
end.setUTCHours(23, 59, 59, 999)
return { start, end }
}
// Handle "yesterday"
if (lowerRange === 'yesterday') {
const start = new Date(now)
start.setUTCDate(utcDate - 1)
start.setUTCHours(0, 0, 0, 0)
const end = new Date(now)
end.setUTCDate(utcDate - 1)
end.setUTCHours(23, 59, 59, 999)
return { start, end }
}
// Handle "this week" (Monday to Sunday)
if (lowerRange === 'this week') {
const mondayOffset = utcDay === 0 ? -6 : 1 - utcDay // If Sunday, go back 6 days, otherwise go to Monday
const start = new Date(now)
start.setUTCDate(utcDate + mondayOffset)
start.setUTCHours(0, 0, 0, 0)
const end = new Date(start)
end.setUTCDate(start.getUTCDate() + 6) // Sunday
end.setUTCHours(23, 59, 59, 999)
return { start, end }
}
// Handle "this month"
if (lowerRange === 'this month') {
const start = new Date(Date.UTC(utcYear, utcMonth, 1, 0, 0, 0, 0))
const end = new Date(Date.UTC(utcYear, utcMonth + 1, 0, 23, 59, 59, 999))
return { start, end }
}
// Handle "this quarter"
if (lowerRange === 'this quarter') {
const quarter = Math.floor(utcMonth / 3)
const start = new Date(Date.UTC(utcYear, quarter * 3, 1, 0, 0, 0, 0))
const end = new Date(Date.UTC(utcYear, quarter * 3 + 3, 0, 23, 59, 59, 999))
return { start, end }
}
// Handle "this year"
if (lowerRange === 'this year') {
const start = new Date(Date.UTC(utcYear, 0, 1, 0, 0, 0, 0))
const end = new Date(Date.UTC(utcYear, 11, 31, 23, 59, 59, 999))
return { start, end }
}
// Handle "last N days" pattern
const lastDaysMatch = lowerRange.match(/^last\s+(\d+)\s+days?$/)
if (lastDaysMatch) {
const days = parseInt(lastDaysMatch[1], 10)
const start = new Date(now)
start.setUTCDate(utcDate - days + 1) // Include today in the count
start.setUTCHours(0, 0, 0, 0)
const end = new Date(now)
end.setUTCHours(23, 59, 59, 999)
return { start, end }
}
// Handle "last N weeks" pattern
const lastWeeksMatch = lowerRange.match(/^last\s+(\d+)\s+weeks?$/)
if (lastWeeksMatch) {
const weeks = parseInt(lastWeeksMatch[1], 10)
const days = weeks * 7
const start = new Date(now)
start.setUTCDate(utcDate - days + 1) // Include today in the count
start.setUTCHours(0, 0, 0, 0)
const end = new Date(now)
end.setUTCHours(23, 59, 59, 999)
return { start, end }
}
// Handle "last week" (previous Monday to Sunday)
if (lowerRange === 'last week') {
const lastMondayOffset = utcDay === 0 ? -13 : -6 - utcDay // Go to previous Monday
const start = new Date(now)
start.setUTCDate(utcDate + lastMondayOffset)
start.setUTCHours(0, 0, 0, 0)
const end = new Date(start)
end.setUTCDate(start.getUTCDate() + 6) // Previous Sunday
end.setUTCHours(23, 59, 59, 999)
return { start, end }
}
// Handle "last month"
if (lowerRange === 'last month') {
const start = new Date(Date.UTC(utcYear, utcMonth - 1, 1, 0, 0, 0, 0))
const end = new Date(Date.UTC(utcYear, utcMonth, 0, 23, 59, 59, 999))
return { start, end }
}
// Handle "last quarter"
if (lowerRange === 'last quarter') {
const currentQuarter = Math.floor(utcMonth / 3)
const lastQuarter = currentQuarter === 0 ? 3 : currentQuarter - 1
const year = currentQuarter === 0 ? utcYear - 1 : utcYear
const start = new Date(Date.UTC(year, lastQuarter * 3, 1, 0, 0, 0, 0))
const end = new Date(Date.UTC(year, lastQuarter * 3 + 3, 0, 23, 59, 59, 999))
return { start, end }
}
// Handle "last year"
if (lowerRange === 'last year') {
const start = new Date(Date.UTC(utcYear - 1, 0, 1, 0, 0, 0, 0))
const end = new Date(Date.UTC(utcYear - 1, 11, 31, 23, 59, 59, 999))
return { start, end }
}
// Handle "last 12 months" (rolling 12 months)
if (lowerRange === 'last 12 months') {
const start = new Date(Date.UTC(utcYear, utcMonth - 11, 1, 0, 0, 0, 0))
const end = new Date(now)
end.setUTCHours(23, 59, 59, 999)
return { start, end }
}
// Handle "last N months" pattern (legacy support)
const lastMonthsMatch = lowerRange.match(/^last\s+(\d+)\s+months?$/)
if (lastMonthsMatch) {
const months = parseInt(lastMonthsMatch[1], 10)
const start = new Date(Date.UTC(utcYear, utcMonth - months + 1, 1, 0, 0, 0, 0))
const end = new Date(now)
end.setUTCHours(23, 59, 59, 999)
return { start, end }
}
// Handle "last N years" pattern (legacy support)
const lastYearsMatch = lowerRange.match(/^last\s+(\d+)\s+years?$/)
if (lastYearsMatch) {
const years = parseInt(lastYearsMatch[1], 10)
const start = new Date(Date.UTC(utcYear - years, 0, 1, 0, 0, 0, 0))
const end = new Date(now)
end.setUTCHours(23, 59, 59, 999)
return { start, end }
}
return null
}
/**
* Normalize date values to handle strings, numbers, and Date objects
* Returns ISO string for PostgreSQL/MySQL, Unix timestamp for SQLite, or null
* Ensures dates are in the correct format for each database engine
*/
normalizeDate(value: any): string | number | null {
Iif (!value) return null
// If it's already a Date object, validate and convert to appropriate format
Iif (value instanceof Date) {
if (isNaN(value.getTime())) return null
// Return timestamp for integer-based databases, ISO string for others
// SQLite stores timestamps as Unix seconds (not milliseconds)
if (this.databaseAdapter.isTimestampInteger()) {
return this.databaseAdapter.getEngineType() === 'sqlite'
? Math.floor(value.getTime() / 1000)
: value.getTime()
}
// PostgreSQL and MySQL need ISO strings, not Date objects
return value.toISOString()
}
// If it's a number, assume it's a timestamp
Iif (typeof value === 'number') {
// If it's a reasonable Unix timestamp in seconds (10 digits), convert to milliseconds
// Otherwise assume it's already in milliseconds
const timestamp = value < 10000000000 ? value * 1000 : value
const date = new Date(timestamp)
if (isNaN(date.getTime())) return null
// Return timestamp for integer-based databases, ISO string for others
// SQLite stores timestamps as Unix seconds (not milliseconds)
if (this.databaseAdapter.isTimestampInteger()) {
return this.databaseAdapter.getEngineType() === 'sqlite'
? Math.floor(timestamp / 1000)
: timestamp
}
// PostgreSQL and MySQL need ISO strings, not Date objects
return date.toISOString()
}
// If it's a string, try to parse it as a Date
Eif (typeof value === 'string') {
// Check if it's a date-only string (YYYY-MM-DD format)
// Parse as UTC midnight to avoid timezone/DST issues
if (/^\d{4}-\d{2}-\d{2}$/.test(value.trim())) {
const parsed = new Date(value + 'T00:00:00Z')
if (isNaN(parsed.getTime())) return null
// Return timestamp for integer-based databases, ISO string for others
// SQLite stores timestamps as Unix seconds (not milliseconds)
if (this.databaseAdapter.isTimestampInteger()) {
return this.databaseAdapter.getEngineType() === 'sqlite'
? Math.floor(parsed.getTime() / 1000)
: parsed.getTime()
}
// PostgreSQL and MySQL need ISO strings, not Date objects
return parsed.toISOString()
}
// For other formats (with time components), use default parsing
const parsed = new Date(value)
if (isNaN(parsed.getTime())) return null
// Return timestamp for integer-based databases, ISO string for others
// SQLite stores timestamps as Unix seconds (not milliseconds)
if (this.databaseAdapter.isTimestampInteger()) {
return this.databaseAdapter.getEngineType() === 'sqlite'
? Math.floor(parsed.getTime() / 1000)
: parsed.getTime()
}
// PostgreSQL and MySQL need ISO strings, not Date objects
return parsed.toISOString()
}
// Try to parse any other type as date
const parsed = new Date(value)
if (isNaN(parsed.getTime())) return null
// Return timestamp for integer-based databases, ISO string for others
// SQLite stores timestamps as Unix seconds (not milliseconds)
if (this.databaseAdapter.isTimestampInteger()) {
return this.databaseAdapter.getEngineType() === 'sqlite'
? Math.floor(parsed.getTime() / 1000)
: parsed.getTime()
}
// PostgreSQL and MySQL need ISO strings, not Date objects
return parsed.toISOString()
}
}
|