- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Notify Moderator
Underlying treatment of dates
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Notify Moderator
Hi there,
When working through a question with our team on how Excel & MS SQL represent dates, we did a quick test and confirmed that SQL and Excel are both storing dates & date-times as a number (technically the offset from a fixed date) which really helps for things like BI applications where a fact table may store a very large number of dates on each record (entered date/time; updated date/time; transaction date/time; etc)
However, when we look at the same in Alteryx, it seems to be storing these dates as plain text (see screenshot below) - meaning that instead of an 8 byte field for every date and datetime; which can be compressed using offset logic like in Parquet, these appear to be represented as a 19 byte field for date-time.
Would it make sense to change the internal representation to a number to make date-offsetting and processing easier (all date-logic then becomes simple addition / subtraction instead of string manipulation)?
Note: You can see this in the screenshot below. the date field has 10 bytes; and the date-time has 19 bytes (where both of these are stored and represented in MSSQL in 8 bytes in total)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.