com presentations: Added the TZ in Agg presentation: mongo-tz-in-3.6.xml slides/mongodb/tz-in-agg-about-tz.xml slides/mongodb/tz-in-agg-date-extraction.xml slides/mongodb/tz-in-agg-date-from-parts.xml slides/mongodb/tz-in-agg-date-from-string-with-tz.xml slides/mongodb/tz-in-agg-date-from-string.xml slides/mongodb/tz-in-agg-date-to-parts.xml slides/mongodb/tz-in-agg-date-to-string.xml slides/mongodb/tz-in-agg-how.xml slides/mongodb/tz-in-agg-reas

diff --git a/mongo-tz-in-3.6.xml b/mongo-tz-in-3.6.xml
new file mode 100644
index 0000000..3128379
--- /dev/null
+++ b/mongo-tz-in-3.6.xml
@@ -0,0 +1,27 @@
+<?xml version="1.0" encoding="utf-8"?>
+<presentation css="10gen-strict.css">
+<topic>MongoDB</topic>
+<title>Time Zone support in Aggregation</title>
+<event>Drivers Meeting</event>
+<location>online</location>
+<date>July 27th, 2017</date>
+<speaker>Derick Rethans</speaker>
+<email>[email protected]</email>
+<twitter>derickr</twitter>
+<url>http://derickrethans.nl/talks.html</url>
+<joindin></joindin>
+<slide>slides/mongodb/title.xml</slide>
+
+<slide>slides/mongodb/tz-in-agg-reason.xml</slide>
+<slide>slides/mongodb/tz-in-agg-about-tz.xml</slide>
+<slide>slides/time/problems-condensed.xml</slide>
+<slide>slides/mongodb/tz-in-agg-how.xml</slide>
+<slide>slides/mongodb/tz-in-agg-date-extraction.xml</slide>
+<slide>slides/mongodb/tz-in-agg-date-to-string.xml</slide>
+<slide>slides/mongodb/tz-in-agg-date-from-string.xml</slide>
+<slide>slides/mongodb/tz-in-agg-date-from-string-with-tz.xml</slide>
+<slide>slides/mongodb/tz-in-agg-date-to-parts.xml</slide>
+<slide>slides/mongodb/tz-in-agg-date-from-parts.xml</slide>
+
+<slide>slides/mongodb/questions.xml</slide>
+</presentation>
diff --git a/slides/mongodb/tz-in-agg-about-tz.xml b/slides/mongodb/tz-in-agg-about-tz.xml
new file mode 100644
index 0000000..4c8f1bb
--- /dev/null
+++ b/slides/mongodb/tz-in-agg-about-tz.xml
@@ -0,0 +1,33 @@
+<slide>
+<title>What are time zones?</title>
+
+<list>
+	<bullet>A set of rules indicating between which times which UTC offset is applicable</bullet>
+	<bullet>Indicated by a %Region%/%Area% identifier<br/>(like %Europe/Madrid%, or %America/Los_Angeles%)</bullet>
+</list>
+
+<break/>
+
+<example>
+derick@singlemalt:~ $ zdump -v Europe/Madrid
+</example>
+
+<example>
+Mon Dec 31 23:59:59 1900 UT = Mon Dec 31 23:45:15 1900 LMT isdst=0 gmtoff=-884
+Tue Jan  1 00:00:00 1901 UT = Tue Jan  1 00:00:00 1901 WET isdst=0 gmtoff=0
+Mon Apr 15 22:59:59 1918 UT = Mon Apr 15 22:59:59 1918 WET isdst=0 gmtoff=0
+Mon Apr 15 23:00:00 1918 UT = Tue Apr 16 00:00:00 1918 WEST isdst=1 gmtoff=3600
+Sun Oct  6 23:59:59 1918 UT = Mon Oct  7 00:59:59 1918 WEST isdst=1 gmtoff=3600
+Mon Oct  7 00:00:00 1918 UT = Mon Oct  7 00:00:00 1918 WET isdst=0 gmtoff=0
+Sun Apr  6 22:59:59 1919 UT = Sun Apr  6 22:59:59 1919 WET isdst=0 gmtoff=0
+...
+Sun Oct 29 00:59:59 2017 UT = Sun Oct 29 02:59:59 2017 CEST isdst=1 gmtoff=7200
+Sun Oct 29 01:00:00 2017 UT = Sun Oct 29 02:00:00 2017 CET isdst=0 gmtoff=3600
+Sun Mar 25 00:59:59 2018 UT = Sun Mar 25 01:59:59 2018 CET isdst=0 gmtoff=3600
+Sun Mar 25 01:00:00 2018 UT = Sun Mar 25 03:00:00 2018 CEST isdst=1 gmtoff=7200
+Sun Oct 28 00:59:59 2018 UT = Sun Oct 28 02:59:59 2018 CEST isdst=1 gmtoff=7200
+Sun Oct 28 01:00:00 2018 UT = Sun Oct 28 02:00:00 2018 CET isdst=0 gmtoff=3600
+Sun Mar 31 00:59:59 2019 UT = Sun Mar 31 01:59:59 2019 CET isdst=0 gmtoff=3600
+...
+</example>
+</slide>
diff --git a/slides/mongodb/tz-in-agg-date-extraction.xml b/slides/mongodb/tz-in-agg-date-extraction.xml
new file mode 100644
index 0000000..05be798
--- /dev/null
+++ b/slides/mongodb/tz-in-agg-date-extraction.xml
@@ -0,0 +1,90 @@
+<slide>
+<title>Date Extraction Operators</title>
+
+<div effect="fade-out">
+<blurb>In MongoDB 3.4:</blurb>
+
+<!--
+    // Convert unix time stamp to ISODate
+    { $project: { date: {
+        $dateFromString: {
+            dateString: {
+                $concat: [ '@', { $substr: [ '$createdAt', 0, -1 ] } ]
+            }
+        } 
+    } } },
+    // Extract venue and hour
+-->
+<example inline="1">
+db.foursquare.aggregate( [
+    { $project: {
+        _id: 0,
+        venue: '$venue.name',
+        *hour: { $hour: '$date' }*
+    } }
+] );
+</example>
+
+<example>
+{ "venue" : "London Paddington Railway Station (PAD)", "hour" : 8 }
+{ "venue" : "Platform 7 (Heathrow Express)", "hour" : 8 }
+{ "venue" : "Heathrow Express Train - Paddington [PAD] to Heathrow [HXX]", "hour" : 8 }
+{ "venue" : "SkyTeam Lounge", "hour" : 9 }
+{ "venue" : "Gate 15", "hour" : 10 }
+{ "venue" : "Gate D23", "hour" : 13 }
+{ "venue" : "Amsterdam Airport Schiphol (AMS)", "hour" : 13 }
+{ "venue" : "Station Amsterdam Lelylaan", "hour" : 13 }
+{ "venue" : "Station Amsterdam Sloterdijk", "hour" : 13 }
+{ "venue" : "Station Amsterdam Centraal", "hour" : 13 }
+{ "venue" : "Burgermeester", "hour" : 16 }
+{ "venue" : "De Eland", "hour" : 17 }
+...
+</example>
+</div>
+
+<div effect="fade-in-out">
+<blurb>In MongoDB 3.6:</blurb>
+
+<example inline="1">
+db.foursquare.aggregate( [
+    { $project: {
+        _id: 0,
+        venue: '$venue.name',
+        hour: { $hour: *{ date: '$date' }* }
+    } }
+] );
+</example>
+</div>
+
+<div effect="fade-in">
+<blurb>In MongoDB 3.6:</blurb>
+
+<example inline="1">
+db.foursquare.aggregate( [
+    { $project: {
+        _id: 0,
+        venue: '$venue.name',
+        hour: { $hour: { date: '$date', *timezone: 'Europe/London'* } }
+    } }
+] );
+</example>
+
+<example>
+{ "venue" : "London Paddington Railway Station (PAD)", "hour" : 9 }
+{ "venue" : "Platform 7 (Heathrow Express)", "hour" : 9 }
+{ "venue" : "Heathrow Express Train - Paddington [PAD] to Heathrow [HXX]", "hour" : 9 }
+{ "venue" : "SkyTeam Lounge", "hour" : 10 }
+{ "venue" : "Gate 15", "hour" : 11 }
+{ "venue" : "Gate D23", "hour" : 14 }
+{ "venue" : "Amsterdam Airport Schiphol (AMS)", "hour" : 14 }
+{ "venue" : "Station Amsterdam Lelylaan", "hour" : 14 }
+{ "venue" : "Station Amsterdam Sloterdijk", "hour" : 14 }
+{ "venue" : "Station Amsterdam Centraal", "hour" : 14 }
+{ "venue" : "Burgermeester", "hour" : 17 }
+{ "venue" : "De Eland", "hour" : 18 }
+{ "venue" : "WEBclusive HQ", "hour" : 12 }
+{ "venue" : "Station Utrecht Centraal", "hour" : 17 }
+...
+</example>
+</div>
+</slide>
diff --git a/slides/mongodb/tz-in-agg-date-from-parts.xml b/slides/mongodb/tz-in-agg-date-from-parts.xml
new file mode 100644
index 0000000..3c2794d
--- /dev/null
+++ b/slides/mongodb/tz-in-agg-date-from-parts.xml
@@ -0,0 +1,24 @@
+<slide>
+<title>New $dateFromParts operator</title>
+
+<blurb>Creates an ISODate from parts + timezone</blurb>
+
+<example inline="1">
+db.tz.aggregate( [
+    { $project: {
+        date: { *$dateFromParts*: {
+            'year': 2017, 'month': 2, 'day': 8, 'hour': 13, 'minute': 7,
+            'timezone': 'America/New_York'
+        } }
+    } }
+] );
+</example>
+
+<blurb>
+Which returns:
+</blurb>
+
+<example>
+{ date: ISODate( "2017-02-08T18:07:00Z" ) }
+</example>
+</slide>
diff --git a/slides/mongodb/tz-in-agg-date-from-string-with-tz.xml b/slides/mongodb/tz-in-agg-date-from-string-with-tz.xml
new file mode 100644
index 0000000..f78a662
--- /dev/null
+++ b/slides/mongodb/tz-in-agg-date-from-string-with-tz.xml
@@ -0,0 +1,62 @@
+<slide>
+<title>New $dateFromString operator</title>
+
+<div effect="fade-out">
+<blurb>Convert integer (as Unix time stamp) to ISODate:</blurb>
+
+<example inline="1">
+db.foursquare.aggregate( [
+    { $project: { date: {
+        $dateFromString: {
+            *dateString: {*
+                *$concat: [ '@', { $substr: [ '$createdAt', 0, -1 ] } ]*
+            *}*
+        } 
+    } } },
+] );
+</example>
+</div>
+
+<div effect="fade-in-out">
+<blurb>Expressed in a different time zone:</blurb>
+
+<example inline="1">
+db.foursquare.aggregate( [
+    { $project: {
+        _id: 0,
+        dateInLondon: {
+            $dateFromString: {
+                dateString: "2017-07-27 16:22:27.132",
+                *timezone: "Europe/London"*
+            }
+        }
+    } },
+    { $limit: 1 }
+] );
+</example>
+<example>
+{ "dateInLondon" : ISODate("2017-07-27T15:22:27.132Z") }
+</example>
+</div>
+<div effect="fade-in">
+<blurb>Expressed in a different time zone:</blurb>
+
+<example inline="1">
+db.foursquare.aggregate( [
+    { $project: {
+        _id: 0,
+        dateInLondon: {
+            $dateFromString: {
+                dateString: "2017-07-27 16:22:27.132",
+                *timezone: "+01:00"*
+            }
+        }
+    } },
+    { $limit: 1 }
+] );
+</example>
+<example>
+{ "dateInLondon" : ISODate("2017-07-27T15:22:27.132Z") }
+</example>
+</div>
+</slide>
diff --git a/slides/mongodb/tz-in-agg-date-from-string.xml b/slides/mongodb/tz-in-agg-date-from-string.xml
new file mode 100644
index 0000000..eac2031
--- /dev/null
+++ b/slides/mongodb/tz-in-agg-date-from-string.xml
@@ -0,0 +1,23 @@
+<slide>
+<title>New $dateFromString operator</title>
+
+<blurb>Creating a date from a string</blurb>
+
+<example inline="1">
+db.foursquare.aggregate( [
+    { $project: {
+        _id: 0,
+        may17th:     { *$dateFromString: { dateString: "2017-05-17" }* },
+        july4th:     { *$dateFromString: { dateString: "4/7/2017" }* },
+        nextWeekDay: { *$dateFromString: { dateString: "2017-07-27 next weekday" }* },
+    } },
+    { $limit: 1 }
+] );
+</example>
+
+<example>
+{ "may17th" : ISODate("2017-05-17T00:00:00Z"),
+  "july4th" : ISODate("2017-04-07T00:00:00Z"),
+  "nextWeekDay" : ISODate("2017-07-28T00:00:00Z") }
+</example>
+</slide>
diff --git a/slides/mongodb/tz-in-agg-date-to-parts.xml b/slides/mongodb/tz-in-agg-date-to-parts.xml
new file mode 100644
index 0000000..c9221e9
--- /dev/null
+++ b/slides/mongodb/tz-in-agg-date-to-parts.xml
@@ -0,0 +1,48 @@
+<slide>
+<title>New $dateToParts operator</title>
+
+<blurb>Converts a date into its consituent parts.<br/><br/>A short cut for having to use a date extracting operator for each element:</blurb>
+
+<div effect="fade-out">
+<example inline="1">
+db.cities.aggregate( [
+    { $project: {
+        _id: 0,
+        blownUp: { *$dateToParts:* {
+            date: ISODate(),
+            timezone: "-04:30"
+
+        } }
+    } }
+] );
+</example>
+<example>
+{ "blownUp" : {
+    "year" : 2017, "month" : 7, "day" : 27, 
+    "hour" : 11, "minute" : 3, "second" : 54, "millisecond" : 966
+} }
+</example>
+</div>
+
+<div effect="fade-in">
+<example inline="1">
+db.cities.aggregate( [
+    { $project: {
+        _id: 0,
+        blownUp: { *$dateToParts:* {
+            date: ISODate(),
+            timezone: "-04:30",
+            *iso8601: true,*
+        } }
+    } }
+] );
+</example>
+<example>
+{ "blownUp" : {
+    "isoYear" : 2017, "isoWeekYear" : 30, "isoDayOfWeek" : 4,
+    "hour" : 11, "minute" : 6, "second" : 44, "millisecond" : 502
+} }
+</example>
+</div>
+
+</slide>
diff --git a/slides/mongodb/tz-in-agg-date-to-string.xml b/slides/mongodb/tz-in-agg-date-to-string.xml
new file mode 100644
index 0000000..00c6957
--- /dev/null
+++ b/slides/mongodb/tz-in-agg-date-to-string.xml
@@ -0,0 +1,30 @@
+<slide>
+<title>Additions to $dateToString</title>
+
+<example inline="1">
+db.cities.aggregate( [
+    { $match: { population: { $gte: 1000000 } } },
+    { $sort: { population: -1 } },
+    { $project: {
+        _id: 0,
+        name: '$name',
+        time: {
+            *$dateToString:* {
+                date: ISODate(),
+                format: '\%Y-\%m-\%d \%H:\%M:\%S *%z*',
+                *timezone: '$timezone'*
+            }
+        }
+    } }
+] );
+</example>
+
+<example>{ "name" : "Shanghai", "time" : "2017-07-27 22:49:24 +0800" }
+{ "name" : "Buenos Aires", "time" : "2017-07-27 11:49:24 -0300" }
+{ "name" : "Mumbai", "time" : "2017-07-27 20:19:24 +0530" }
+{ "name" : "Mexico City", "time" : "2017-07-27 09:49:24 -0500" }
+{ "name" : "Karachi", "time" : "2017-07-27 19:49:24 +0500" }
+{ "name" : "İstanbul", "time" : "2017-07-27 17:49:24 +0300" }
+…
+</example>
+</slide>
diff --git a/slides/mongodb/tz-in-agg-how.xml b/slides/mongodb/tz-in-agg-how.xml
new file mode 100644
index 0000000..f47366c
--- /dev/null
+++ b/slides/mongodb/tz-in-agg-how.xml
@@ -0,0 +1,15 @@
+<slide>
+<title>How?</title>
+
+<list>
+    <bullet>Vendorize a library: %timelib%</bullet>
+    <bullet>DST calculations, parsing, etc.</bullet>
+    <bullet>Also used in PHP and HHVM</bullet>
+    <bullet>Comes with built-in database,<br/>but also: %/usr/share/zoneinfo% and archive</bullet>
+    <bullet>%processManagement.timeZoneInfo%</bullet>
+</list>
+<break/>
+<list>
+    <bullet>Project as part of the Server-Query team</bullet>
+</list>
+</slide>
diff --git a/slides/mongodb/tz-in-agg-reason.xml b/slides/mongodb/tz-in-agg-reason.xml
new file mode 100644
index 0000000..8ba13b6
--- /dev/null
+++ b/slides/mongodb/tz-in-agg-reason.xml
@@ -0,0 +1,10 @@
+<slide>
+<title>Why?</title>
+
+<list>
+	<bullet>MongoDB stores date information solely as a Unix time stamp</bullet>
+	<bullet>Microseconds since 1970-01-01 00:00 UTC</bullet>
+	<bullet>No way to do a group-by-day in any other time zone without hacks</bullet>
+	<bullet>Can't output in anything else besides UTC</bullet>
+</list>
+</slide>