{"id":18589,"date":"2022-09-06T09:05:00","date_gmt":"2022-09-06T07:05:00","guid":{"rendered":"https:\/\/www.codemotion.com\/magazine\/?p=18589"},"modified":"2023-06-23T15:19:57","modified_gmt":"2023-06-23T13:19:57","slug":"date-intervals-in-data-warehouses-and-data-lakes","status":"publish","type":"post","link":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/","title":{"rendered":"Working with Date Intervals in Data Warehouses and Data Lakes"},"content":{"rendered":"\n

Working as Data Engineer<\/a> makes you work with dates and time data a lot. Especially in the recent period where companies want to be Data-Driven, the software is Event-driven, your coffee machine is data-driven, and AI and ML require tons of data to work. In this article, I will share my experience working with date intervals in data warehouses and data lakes hoping to make your queries a little bit easier.<\/p>\n\n\n\n

Motivation<\/h2>\n\n\n\n

Very often with modern software architecture like Event Driven Systems<\/a> or with more legacy data warehouse or data lake modeling techniques we might end up having tables where each record has a validity period (i.e. a start date and an end date defining the interval where the record information is true in the system). <\/p>\n\n\n\n

In a data warehouse and data lake context<\/a>, modeling data using a type of slow-changing dimension<\/a> (SCD) is quite a popular choice to track your data history. For example, suppose you have clients registry data, and each time a client changes his shipping address then you set the end_date of the previous shipping address and the start_date of the
new record with the timestamp the modification inside the address_tbl<\/p>\n\n\n\n

<\/p>\n\n\n\n

\n
client_id<\/th>shipping_address<\/th>start_timestamp<\/th>end_timestamp<\/th><\/tr><\/thead>
client_1<\/td>383 Trusel DriveNorth Andover, MA 01845<\/td>2015-01-14 00:00:00<\/td>2017-05-11 10:00:00<\/td><\/tr>
client_1<\/td>91 Gates St. Valrico, FL 33594<\/td>2017-05-11 10:00:00<\/td>2999-01-01 23:59:59<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/div><\/div>\n\n\n\n

Then you know that a client_1’s current shipping_address is the one satisfying the condition<\/p>\n\n\n

SELECT *\nFROM address_tbl\nWHERE\n client_id=client_1 and\n address_tbl.start_date <= current_timestamp() < address_tbl.end_date<\/code><\/span><\/pre>\n\n\n
In the case of an event-driven system you have the same pieces of information represented as events in the address_evt_tbl:<\/p>\n\n\n\n
client_id<\/th>shipping_address<\/th>event_timestamp<\/th><\/tr><\/thead>
client_1<\/td>383 Trusel DriveNorth Andover, MA 01845<\/td>2015-01-14 00:00:00<\/td><\/tr>
client_1<\/td>2015-01-14 00:00:00<\/td>2017-05-11 10:00:00<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Then when you use that data for analytics or modeling in general you end up transforming the event table into a table with validity intervals with a query like:<\/p>\n\n\n
WITH address_tbl AS<\/span>(\n SELECT client_id, \n shipping_address, \n event_timestamp AS<\/span> start_timestamp,\n COALESCE(LEAD(event_timestamp) OVER (PARTITION BY client_id ORDER BY event_timestamp), '2999-01-01 23:59:59'<\/span>) as<\/span>\nend_timestamp\n FROM address_evt_tbl\n)\n--As<\/span> before, you know that a client_1's current shipping_address is the one satisfying the condition \nSELECT *\nFROM address_tbl\nWHERE\n client_id=client_1 and\n address_tbl.start_date <= current_timestamp() < address_tbl.end_date <\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Now suppose you have another table called fav_payment_method<\/strong> telling your client’s favorite payment method:<\/p>\n\n\n\n
client_id<\/th>start_timestamp<\/th>end_timestamp<\/th>payment_method<\/th><\/tr><\/thead>
client_1<\/td>2015-01-01 00:00:00<\/td>2016-01-01 00:00:00<\/td>at_delivery_time<\/td><\/tr>
client_1<\/td>2016-01-01 00:00:00<\/td>2018-01-01 10:00:00<\/td>paypal<\/td><\/tr>
client_1<\/td>2018-01-01 10:00:00<\/td>2019-01-01 23:00:00<\/td>credit card<\/td><\/tr>
client_1<\/td>2019-01-01 23:00:00<\/td>2999-01-01 23:59:59<\/td>Paypal<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
And, don’t ask your Boss why, but you have to associate each client’s shipping address with his favorite payment method and how they evolve over time. What is the
best way to perform this operation in SQL or frameworks with SQL-like interface?<\/p>\n\n\n\n
Naive solution<\/h2>\n\n\n\n
The first idea we have is to enumerate all the possibilities. For example, we might write:<\/p>\n\n\n
SELECT * \nFROM address_tbl at INNER JOIN fav_payment_method fpm\n ON at.client_id = fpm.client_id\nWHERE (at.start_timestamp >= fpm.start_timestamp AND\n at.start_timestamp < fpm.end_timestamp<\/span>) OR<\/span> --<\/span> (1<\/span>)\n (at.end_timestamp<\/span> ><\/span>= fpm.start_timestamp AND\n at.end_timestamp < fpm.end_timestamp<\/span>) OR<\/span> --<\/span> (2<\/span> )\n (fpm.start_timestamp<\/span> ><\/span>= at.start_timestamp AND\n fpm.start_timestamp < at.end_timestamp<\/span>) OR<\/span> --<\/span> (3<\/span>)\n (fpm.end_timestamp<\/span> ><\/span>= at.start_timestamp AND\n fpm.end_timestamp < at.end_timestamp<\/span>) --<\/span> (4<\/span>)\n<\/span><\/code><\/span>Code language:<\/span> HTML, XML<\/span> (<\/span>xml<\/span>)<\/span><\/small><\/pre>\n\n\n
That can be represented graphically with the following segments (address_tbl in red and fav_payment_method in blue<\/strong>)<\/p>\n\n\n\n
<\/p>\n\n\n\n
\"data<\/figure>\n\n\n\n
\"data<\/figure>\n\n\n\n
We can see that the query has a lot of conditions to match the records taking into account the validity periods and we are not even sure it covers all the cases. Do not mention the case when you inherit the code from a colleague who has quit the company and you have to interpret it\u2026<\/p>\n\n\n\n
Second try<\/h2>\n\n\n\n
But indeed we can be less naive than this. It turns out that the problem is a 1D segment intersection problem. Segments are your ranges of dates and you only need to take the periods that intersect. But how can we write that? And can we do it simply, maybe without enumerating all the possibilities? Well, we can say that two segments intersect if and only if they not(not intersect)<\/strong>. Ah-ah, simple right?<\/p>\n\n\n\n
<\/p>\n\n\n\n
\"data<\/figure>\n\n\n\n
In the image we can see there isn’t an intersection when:<\/p>\n\n\n
at<\/span>.start_timestamp<\/span> > fpm<\/span>.end_timestamp<\/span> or<\/span> at<\/span>.end_timestamp<\/span> < fpm<\/span>.start_timestamp<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
This also means that negating this condition we obtain not(not intersect)<\/strong><\/p>\n\n\n
at.start_timestamp <=<\/span> fpm.end_timestamp<\/span> and<\/span> at.end_timestamp<\/span> ><\/span>= fpm.start_timestamp<\/code><\/span>Code language:<\/span> HTML, XML<\/span> (<\/span>xml<\/span>)<\/span><\/small><\/pre>\n\n\n
And we can finally write a simple, clean and concise query that does exactly what we wanted:<\/p>\n\n\n
SELECT * \nFROM address_tbl at INNER JOIN fav_payment_method fpm\n ON at.client_id = fpm.client_id\nWHERE at.start_timestamp <=<\/span> fpm.end_timestamp<\/span> and<\/span> \n at.end_timestamp<\/span> ><\/span>= fpm.start_timestamp<\/code><\/span>Code language:<\/span> HTML, XML<\/span> (<\/span>xml<\/span>)<\/span><\/small><\/pre>\n\n\n
fpm.client_id<\/th>fpm.start_timestamp<\/th>fpm.end_timestamp<\/th>fpm.payment_method<\/th>at.client_id<\/th>at.shipping_address<\/th>at.start_timestamp<\/th>at.end_times<\/th><\/tr><\/thead>
client_1<\/td>2015-01-01 00:00:00<\/td>2016-01-01 00:00:00<\/td>at_delivery_time<\/td>client_1<\/td>383 Trusel
DriveNorth Andover,
MA 01845<\/td>		2015-01-14
00:00:00<\/td>		2017-05-11
10:00:00<\/td><\/tr>
client_1<\/td>2016-01-01 00:00:00<\/td>2018-01-01 10:00:00<\/td>paypal<\/td>client_1<\/td>383 Trusel
DriveNorth Andover,
MA 01845<\/td>		2015-01-14
00:00:00<\/td>		2017-05-11
10:00:00<\/td><\/tr>
client_1<\/td>2016-01-01 00:00:00<\/td>2018-01-01 10:00:00<\/td>paypal<\/td>client_1<\/td>91 Gates St. Valrico,
FL 33594<\/td>		2017-05-11
10:00:00<\/td>		2999-01-01
23:59:59<\/td><\/tr>
client_1<\/td>2018-01-01 10:00:00<\/td>2019-01-01 23:00:00<\/td>credit card<\/td>client_1<\/td>91 Gates St. Valrico,
FL 33594<\/td>		2017-05-11
10:00:00<\/td>		2999-01-01
23:59:59<\/td><\/tr>
client_1<\/td>2019-01-01 23:00:00<\/td>2999-01-01 23:59:59<\/td>paypal<\/td>client_1<\/td>91 Gates St. Valrico,
FL 33594<\/td>		2017-05-11
10:00:00<\/td>		2999-01-01
23:59:59<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Display them nicely<\/h2>\n\n\n\n
So far so good, but then the final table is a mess, there are four timestamps, what should we do next? We should go one step further and give unique, continuous, and
coherent validity intervals. But how?<\/p>\n\n\n\n
\"data<\/figure>\n\n\n\n
Looking at the picture we can compute the intersection of the date segments to get the new periods for each record. The intersection of 1D segments is $$
intersection_start_timestamp = max(ad.start_timestamp, fpm.start_timestamp)$$ and $$ intersection_end_timestamp = min(ad.end_timestamp, fpm.end_timestamp)$$<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
And the final result is:<\/p>\n\n\n
SELECT fpm.client_id, \n greatest(at.start_timestamp, fpm.start_timestamp) as<\/span> start_timestamp,\n least(at.end_timestamp, fpm.end_timestamp) as<\/span> end_timestamp,\n fpm.payment_method,\n at.shipping_address\nFROM address_tbl at INNER JOIN fav_payment_method fpm\n ON at.client_id = fpm.client_id\nWHERE at.start_timestamp <= fpm.end_timestamp and \n at.end_timestamp >= fpm.start_timestamp\n\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
client_id<\/th>start_timestamp<\/th>end_timestamp<\/th>payment_method<\/th>shipping_address<\/th><\/tr><\/thead>
client_1<\/td>2015-01-01 00:00:00<\/td>2016-01-01 00:00:00<\/td>at_delivery_time<\/td>383 Trusel DriveNorth Andover, MA 01845<\/td><\/tr>
client_1<\/td>2016-01-01 00:00:00<\/td>2017-05-11 10:00:00<\/td>paypal<\/td>383 Trusel DriveNorth Andover, MA 01845<\/td><\/tr>
client_1<\/td>2017-05-11 10:00:00<\/td>2018-01-01 10:00:00<\/td>paypal<\/td>91 Gates St. Valrico, FL 33594<\/td><\/tr>
client_1<\/td>2018-01-01 10:00:00<\/td>2019-01-01 23:00:00<\/td>credit card<\/td>91 Gates St. Valrico, FL 33594<\/td><\/tr>
client_1<\/td>2019-01-01 23:00:00<\/td>2999-01-01 23:59:59<\/td>paypal<\/td>91 Gates St. Valrico, FL 33594<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Note that, since at the end you obtain a table with only one start_timestamp<\/strong> and one end_timestamp<\/strong>, you can iterate this approach indefinitely and obtain a snapshot
for each version of your data.
Now you won’t suffer that much when working with date intervals in data warehouses and data lakes. Have a good query!<\/p>\n","protected":false},"excerpt":{"rendered":"
Working as Data Engineer makes you work with dates and time data a lot. Especially in the recent period where companies want to be Data-Driven, the software is Event-driven, your coffee machine is data-driven, and AI and ML require tons of data to work. In this article, I will share my experience working with date… Read more<\/a><\/p>\n","protected":false},"author":149,"featured_media":18604,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_editorskit_title_hidden":false,"_editorskit_reading_time":5,"_editorskit_is_block_options_detached":false,"_editorskit_block_options_position":"{}","_uag_custom_page_level_css":"","_genesis_hide_title":false,"_genesis_hide_breadcrumbs":false,"_genesis_hide_singular_image":false,"_genesis_hide_footer_widgets":false,"_genesis_custom_body_class":"","_genesis_custom_post_class":"","_genesis_layout":"","footnotes":""},"categories":[8457],"tags":[5571,4209,4446],"collections":[],"class_list":{"0":"post-18589","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-data-science","8":"tag-big-data","9":"tag-careers","10":"tag-data-analysis","11":"entry"},"yoast_head":"\nData Warehouses and Data Lakes: A Guide Key Concepts<\/title>\n<meta name=\"description\" content=\"Code examples and tips for working with date intervales in data warehouses and data lakes. Get it done right with this complete guide!\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Working with Date Intervals in Data Warehouses and Data Lakes\" \/>\n<meta property=\"og:description\" content=\"Code examples and tips for working with date intervales in data warehouses and data lakes. Get it done right with this complete guide!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/\" \/>\n<meta property=\"og:site_name\" content=\"Codemotion Magazine\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/Codemotion.Italy\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-06T07:05:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-06-23T13:19:57+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"640\" \/>\n\t<meta property=\"og:image:height\" content=\"360\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Nicola Di Santo\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@CodemotionIT\" \/>\n<meta name=\"twitter:site\" content=\"@CodemotionIT\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nicola Di Santo\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/\"},\"author\":{\"name\":\"Nicola Di Santo\",\"@id\":\"https:\/\/www.codemotion.com\/magazine\/#\/schema\/person\/5f1b31a32d6dc2927a3b7b0afd9035e0\"},\"headline\":\"Working with Date Intervals in Data Warehouses and Data Lakes\",\"datePublished\":\"2022-09-06T07:05:00+00:00\",\"dateModified\":\"2023-06-23T13:19:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/\"},\"wordCount\":930,\"publisher\":{\"@id\":\"https:\/\/www.codemotion.com\/magazine\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg\",\"keywords\":[\"Big Data\",\"Careers\",\"Data Analysis\"],\"articleSection\":[\"Data Science\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/\",\"url\":\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/\",\"name\":\"Data Warehouses and Data Lakes: A Guide Key Concepts\",\"isPartOf\":{\"@id\":\"https:\/\/www.codemotion.com\/magazine\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg\",\"datePublished\":\"2022-09-06T07:05:00+00:00\",\"dateModified\":\"2023-06-23T13:19:57+00:00\",\"description\":\"Code examples and tips for working with date intervales in data warehouses and data lakes. Get it done right with this complete guide!\",\"breadcrumb\":{\"@id\":\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#primaryimage\",\"url\":\"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg\",\"contentUrl\":\"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg\",\"width\":640,\"height\":360},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.codemotion.com\/magazine\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data Science\",\"item\":\"https:\/\/www.codemotion.com\/magazine\/ai-ml\/data-science\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Working with Date Intervals in Data Warehouses and Data Lakes\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.codemotion.com\/magazine\/#website\",\"url\":\"https:\/\/www.codemotion.com\/magazine\/\",\"name\":\"Codemotion Magazine\",\"description\":\"We code the future. Together\",\"publisher\":{\"@id\":\"https:\/\/www.codemotion.com\/magazine\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.codemotion.com\/magazine\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.codemotion.com\/magazine\/#organization\",\"name\":\"Codemotion\",\"url\":\"https:\/\/www.codemotion.com\/magazine\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.codemotion.com\/magazine\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2019\/11\/codemotionlogo.png\",\"contentUrl\":\"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2019\/11\/codemotionlogo.png\",\"width\":225,\"height\":225,\"caption\":\"Codemotion\"},\"image\":{\"@id\":\"https:\/\/www.codemotion.com\/magazine\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/Codemotion.Italy\/\",\"https:\/\/x.com\/CodemotionIT\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.codemotion.com\/magazine\/#\/schema\/person\/5f1b31a32d6dc2927a3b7b0afd9035e0\",\"name\":\"Nicola Di Santo\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.codemotion.com\/magazine\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/bf7c4c51a5b2debad1d8ef4b1fdf58512204bfc3591af08a94a1e0052a85da54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/bf7c4c51a5b2debad1d8ef4b1fdf58512204bfc3591af08a94a1e0052a85da54?s=96&d=mm&r=g\",\"caption\":\"Nicola Di Santo\"},\"url\":\"https:\/\/www.codemotion.com\/magazine\/author\/nicola-di-santo\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Data Warehouses and Data Lakes: A Guide Key Concepts","description":"Code examples and tips for working with date intervales in data warehouses and data lakes. Get it done right with this complete guide!","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/","og_locale":"en_US","og_type":"article","og_title":"Working with Date Intervals in Data Warehouses and Data Lakes","og_description":"Code examples and tips for working with date intervales in data warehouses and data lakes. Get it done right with this complete guide!","og_url":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/","og_site_name":"Codemotion Magazine","article_publisher":"https:\/\/www.facebook.com\/Codemotion.Italy\/","article_published_time":"2022-09-06T07:05:00+00:00","article_modified_time":"2023-06-23T13:19:57+00:00","og_image":[{"width":640,"height":360,"url":"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg","type":"image\/jpeg"}],"author":"Nicola Di Santo","twitter_card":"summary_large_image","twitter_creator":"@CodemotionIT","twitter_site":"@CodemotionIT","twitter_misc":{"Written by":"Nicola Di Santo","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#article","isPartOf":{"@id":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/"},"author":{"name":"Nicola Di Santo","@id":"https:\/\/www.codemotion.com\/magazine\/#\/schema\/person\/5f1b31a32d6dc2927a3b7b0afd9035e0"},"headline":"Working with Date Intervals in Data Warehouses and Data Lakes","datePublished":"2022-09-06T07:05:00+00:00","dateModified":"2023-06-23T13:19:57+00:00","mainEntityOfPage":{"@id":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/"},"wordCount":930,"publisher":{"@id":"https:\/\/www.codemotion.com\/magazine\/#organization"},"image":{"@id":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#primaryimage"},"thumbnailUrl":"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg","keywords":["Big Data","Careers","Data Analysis"],"articleSection":["Data Science"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/","url":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/","name":"Data Warehouses and Data Lakes: A Guide Key Concepts","isPartOf":{"@id":"https:\/\/www.codemotion.com\/magazine\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#primaryimage"},"image":{"@id":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#primaryimage"},"thumbnailUrl":"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg","datePublished":"2022-09-06T07:05:00+00:00","dateModified":"2023-06-23T13:19:57+00:00","description":"Code examples and tips for working with date intervales in data warehouses and data lakes. Get it done right with this complete guide!","breadcrumb":{"@id":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#primaryimage","url":"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg","contentUrl":"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg","width":640,"height":360},{"@type":"BreadcrumbList","@id":"https:\/\/www.codemotion.com\/magazine\/data-science\/date-intervals-in-data-warehouses-and-data-lakes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.codemotion.com\/magazine\/"},{"@type":"ListItem","position":2,"name":"Data Science","item":"https:\/\/www.codemotion.com\/magazine\/ai-ml\/data-science\/"},{"@type":"ListItem","position":3,"name":"Working with Date Intervals in Data Warehouses and Data Lakes"}]},{"@type":"WebSite","@id":"https:\/\/www.codemotion.com\/magazine\/#website","url":"https:\/\/www.codemotion.com\/magazine\/","name":"Codemotion Magazine","description":"We code the future. Together","publisher":{"@id":"https:\/\/www.codemotion.com\/magazine\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.codemotion.com\/magazine\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.codemotion.com\/magazine\/#organization","name":"Codemotion","url":"https:\/\/www.codemotion.com\/magazine\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.codemotion.com\/magazine\/#\/schema\/logo\/image\/","url":"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2019\/11\/codemotionlogo.png","contentUrl":"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2019\/11\/codemotionlogo.png","width":225,"height":225,"caption":"Codemotion"},"image":{"@id":"https:\/\/www.codemotion.com\/magazine\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/Codemotion.Italy\/","https:\/\/x.com\/CodemotionIT"]},{"@type":"Person","@id":"https:\/\/www.codemotion.com\/magazine\/#\/schema\/person\/5f1b31a32d6dc2927a3b7b0afd9035e0","name":"Nicola Di Santo","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.codemotion.com\/magazine\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/bf7c4c51a5b2debad1d8ef4b1fdf58512204bfc3591af08a94a1e0052a85da54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/bf7c4c51a5b2debad1d8ef4b1fdf58512204bfc3591af08a94a1e0052a85da54?s=96&d=mm&r=g","caption":"Nicola Di Santo"},"url":"https:\/\/www.codemotion.com\/magazine\/author\/nicola-di-santo\/"}]}},"featured_image_src":"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse-600x360.jpg","featured_image_src_square":"https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse-600x360.jpg","author_info":{"display_name":"Nicola Di Santo","author_link":"https:\/\/www.codemotion.com\/magazine\/author\/nicola-di-santo\/"},"uagb_featured_image_src":{"full":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg",640,360,false],"thumbnail":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse-150x150.jpg",150,150,true],"medium":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse-300x169.jpg",300,169,true],"medium_large":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg",640,360,false],"large":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg",640,360,false],"1536x1536":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg",640,360,false],"2048x2048":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg",640,360,false],"small-home-featured":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg",100,56,false],"sidebar-featured":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse-180x128.jpg",180,128,true],"genesis-singular-images":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse.jpg",640,360,false],"archive-featured":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse-400x225.jpg",400,225,true],"gb-block-post-grid-landscape":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse-600x360.jpg",600,360,true],"gb-block-post-grid-square":["https:\/\/www.codemotion.com\/magazine\/wp-content\/uploads\/2022\/08\/data-lakehouse-600x360.jpg",600,360,true]},"uagb_author_info":{"display_name":"Nicola Di Santo","author_link":"https:\/\/www.codemotion.com\/magazine\/author\/nicola-di-santo\/"},"uagb_comment_info":0,"uagb_excerpt":"Working as Data Engineer makes you work with dates and time data a lot. Especially in the recent period where companies want to be Data-Driven, the software is Event-driven, your coffee machine is data-driven, and AI and ML require tons of data to work. In this article, I will share my experience working with date……","lang":"en","_links":{"self":[{"href":"https:\/\/www.codemotion.com\/magazine\/wp-json\/wp\/v2\/posts\/18589","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.codemotion.com\/magazine\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.codemotion.com\/magazine\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.codemotion.com\/magazine\/wp-json\/wp\/v2\/users\/149"}],"replies":[{"embeddable":true,"href":"https:\/\/www.codemotion.com\/magazine\/wp-json\/wp\/v2\/comments?post=18589"}],"version-history":[{"count":11,"href":"https:\/\/www.codemotion.com\/magazine\/wp-json\/wp\/v2\/posts\/18589\/revisions"}],"predecessor-version":[{"id":21571,"href":"https:\/\/www.codemotion.com\/magazine\/wp-json\/wp\/v2\/posts\/18589\/revisions\/21571"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.codemotion.com\/magazine\/wp-json\/wp\/v2\/media\/18604"}],"wp:attachment":[{"href":"https:\/\/www.codemotion.com\/magazine\/wp-json\/wp\/v2\/media?parent=18589"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.codemotion.com\/magazine\/wp-json\/wp\/v2\/categories?post=18589"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.codemotion.com\/magazine\/wp-json\/wp\/v2\/tags?post=18589"},{"taxonomy":"collections","embeddable":true,"href":"https:\/\/www.codemotion.com\/magazine\/wp-json\/wp\/v2\/collections?post=18589"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}