Core FQL concepts, part 1: Working with dates and times
Tags: serverless, fullstack, fql, fauna
Published: 2020-10-02 17:22:59

Author: Pier Bover  
Publish date: Aug 31, 2020

---

Welcome! This is the first article in a new series exploring some core concepts of FQL, the native query language of FaunaDB.

This series will assume you have a grasp on the basics. If you're new to FaunaDB and/or FQL here's [my introductory series on FQL](https://fauna.com/blog/getting-started-with-fql-faunadbs-native-query-language-part-1).

Today we'll explore how to work with dates and timestamps.

##In this article:
* Date and time basics
* Printing date and time
* Time operations
* Sorting time results

##Date and time basics
FaunaDB has two native date types:
* [Date](https://docs.fauna.com/fauna/current/api/fql/types#date) to store a calendar date
* [Timestamp](https://docs.fauna.com/fauna/current/api/fql/types#timestamp) to store a UTC date and time, with nanosecond precision

The most common way to create a new **Date** is by simply passing an ISO date string to the [Date()](https://docs.fauna.com/fauna/current/api/fql/functions/date) function:
```
Date("2020-08-15")
```
Similarly, we can create a new **Timestamp** value by passing an UTC ISO time and date string to the [Time()](https://docs.fauna.com/fauna/current/api/fql/functions/time) function:
```
Time("2020-08-15T18:39:45Z")
```
Timestamps in FaunaDB are always stored in UTC time. If we pass an ISO string with a time zone offset it is automatically converted:
```
> Time('2020-08-15T00:00:00+04:00')
Time("2020-08-14T20:00:00Z")
```
As you can see, the day of the date has changed because the string we passed was 4 hours ahead of UTC time.

It's also possible to use [Epoch()](https://docs.fauna.com/fauna/current/api/fql/functions/epoch) to create a timestamp based on a UNIX time:
```
> Epoch(1597533145964, "millisecond")
Time("2020-08-15T23:12:25.964Z")
```
Since FaunaFB timestamps can store up to nanosecond precision, the **Epoch()** function requires a second argument to determine the unit of the created **Timestamp**.

We can also use [Now()](https://docs.fauna.com/fauna/current/api/fql/functions/now) to create a new **Timestamp** (the FaunaDB type, not the UNIX time) at the current moment in microseconds (1 millisecond = 1000 microseconds):
```
> Now()
Time("2020-08-15T23:04:14.455004Z")
```
**Quick note:** The **Timestamp** produced by **Now()** is based on the transaction time. If you call it multiple times in the same transaction the result will always be the same:
```
> Let(
  {
    now1: Now(),
    now2: Now()
  },
  {
    ts1: Var("now1"),
    ts2: Var("now2")
  }
)
{
  ts1: Time("2020-08-15T23:27:41.885588Z"),
  ts2: Time("2020-08-15T23:27:41.885588Z")
}
```
##Converting between Date and Timestamp types
To convert a **Timestamp** to a **Date** we use the [ToDate()](https://docs.fauna.com/fauna/current/api/fql/functions/todate) function. All of these examples produce the same result:
```
> ToDate(Time("2020-08-15T23:12:25Z"))
Date("2020-08-15")


> ToDate(Epoch(1597533145964, "millisecond"))
Date("2020-08-15")


> ToDate(Now())
Date("2020-08-15")
```
Likewise, to convert a **Date** to a **Timestamp** we use the [ToTime()](https://docs.fauna.com/fauna/current/api/fql/functions/totime) function:
```
> ToTime(Date("2020-08-15"))
Time("2020-08-15T00:00:00Z")
```
##Comparing dates
As expected, we can use **Date** and **Timestamp** types with all of the comparison functions available to us in FQL:
```
>Equals(Now(), Now())
true
```
We can even use [LT()](https://docs.fauna.com/fauna/current/api/fql/functions/lt) and [LTE()](https://docs.fauna.com/fauna/current/api/fql/functions/lte) to know which date is "larger", or more recent:
```
> LT(Now(), Date("1970-11-05"))
true
```
##Printing dates and times
Once you have your **Date** or **Timestamp** values stored in FaunaDB, you probably need to read those in your programming language, or at least present those values directly to your users in a more human way.

The [Format()](https://docs.fauna.com/fauna/current/api/fql/functions/format) function is your bread and butter tool to do that. It's extremely powerful, so we're only going to scratch the surface here.

For example, you might want to get the number of milliseconds since 1970 (UNIX time) which is the type of value you get when doing **Date.now()** in JavaScript:
```
> Format('%tQ', Now())
1597939216796
```
It's also very common to use ISO date strings:
```
> Format('%t', Now())
2020-08-20T16:13:13.654455Z
```
You might also want to print the date in some other format than **YYYY-MM-DD**:
```
> Format('%tD', Now())
08/20/20
```
And again, in the European format:
```
> Format('%td/%tm/%ty', Now(), Now(), Now())
20/08/20
```
As I said, **Format()** is extremely powerful. [Check the docs](https://docs.fauna.com/fauna/current/api/fql/functions/format) for all of the available options to format strings.

##Time operations
FaunaDB has many powerful capabilities to work with dates. Let's see a couple of practical examples.

For the rest of the article we'll just assume that all planets across the galaxy follow Earth's time. My apologies to any Vulkans reading this article.

###Addition
A couple of weeks ago, the fleet installed a teleporter to beam personnel from the home base to spaceships. Obviously people started abusing it since it was too much fun, so the admiral has tasked us to build a little system to make appointments and control the teleporting traffic.

First, let's create a new collection to track the teleportations:
```
> CreateCollection({name:"Teleportations"})
{
  ref: Collection("Teleportations"),
  ts: 1597715786192000,
  history_days: 30,
  name: 'Teleportations'
}
```
Let’s also create a new collection to track the pilots:
```
> CreateCollection({name:"Pilots"})
{
  ref: Collection("Pilots"),
  ts: 1597715790726000,
  history_days: 30,
  name: 'Pilots'
}
```
Let's schedule a teleportation 10 days from now:
```
> Create(
  Collection("Teleportations"),
  {
    data: {
      personRef: Ref(Collection("Pilots"), "266350546751848978"),
      status: 'SCHEDULED',
      ts: TimeAdd(Now(), 10, "days")
    }
  }
)
{
  ref: Ref(Collection("Teleportations"), "274157476972069395"),
  ts: 1597715794460000,
  data: {
    personRef: Ref(Collection("Pilots"), "266350546751848978"),
    status: "SCHEDULED",
    ts: Time("2020-08-28T01:56:34.304009Z")
  }
}
```
This is the interesting part:
```
TimeAdd(Now(), 10, "days")
```
The [TimeAdd()](https://docs.fauna.com/fauna/current/api/fql/functions/timeadd) function takes a **Date** or a **Timestamp** and adds a number of units to it. If you're using dates you have to use days, but with timestamps you can use any unit down to nanoseconds. Check [the documentation](https://docs.fauna.com/fauna/current/api/fql/functions/timeadd) to see all of the available units.

If the teleporter were to undergo some maintenance, we could just reschedule the pending teleportations by adding the duration of the repairs.

For example, here's how we could add 8 hours to the scheduled timestamp:
```
> Let(
  {
    ref: Ref(Collection("Teleportations"), "274157476972069395"),
    ts: Select(["data", "ts"], Get(Var("ref")))
  },
  Update(
    Var("ref"),
    {
      data: {
        ts: TimeAdd(Var("ts"), 8, "hours")
      }
    }
  )
)
{
  ref: Ref(Collection("Teleportations"), "274157476972069395"),
  ts: 1597716265635000,
  data: {
    personRef: Ref(Collection("Pilots"), "266350546751848978"),
    status: "SCHEDULED",
    ts: Time("2020-08-28T09:56:34.304009Z")
  }
}
```
**Quick tip:** The [Let()](https://docs.fauna.com/fauna/current/api/fql/functions/let) function is commonly used to return a custom object, but it actually executes any FQL expression in its second parameter. Here we're using it to execute the update after having collected the necessary data first.

###Subtraction
We can just as easily subtract units of time by using [TimeSubtract()](https://docs.fauna.com/fauna/current/api/fql/functions/timesubtract) which works exactly like **TimeAdd()**:
```
TimeSubtract(Now(), 4, "hours")
```
##Calculating time offsets
Sometimes pilots want to use the teleporter before their scheduled time, and we can't really allow that. We could at least show them how much time is left before they can use it.

As its name implies, we use [TimeDiff()](https://docs.fauna.com/fauna/current/api/fql/functions/timediff) to calculate differences between two times:
```
> TimeDiff(
  Date("2020-08-15"),
  Date("2020-08-20"),
  "days"
)
5
```
Again, if you're using dates you must use day units. You can calculate the offset in any another unit, like say hours, by converting dates to timestamps using **ToTime()**:
```
> TimeDiff(
  ToTime(Date("2020-08-15")),
  Now(),
  "hours"
)
74
```
Another interesting thing to know about **TimeDiff()** is that it always rounds to the lowest whole value:
```
>
Let(
  {
    ts1: Now(),
    ts2: TimeAdd(Now(), 119, "minutes")
  },
  TimeDiff(Var("ts1"), Var("ts2"), "hours")
)
1
```
119 minutes is almost two hours, and yet FaunaDB returns one hour.

Ok, with this in mind let's calculate how much time a pilot has to wait before teleporting:
```
> Let(
  {
    ref: Ref(Collection("Teleportations"), "274157476972069395"),
    ts: Select(["data", "ts"], Get(Var("ref")))
  },
  TimeDiff(Now(), Var("ts"), "minutes")
)
14845
```
Phew! 14,845 minutes is a lot of waiting!

Obviously, we would need to massage that data into hours and minutes before showing it to our user. We could also create a simple user-defined-function (or UDF) like we saw in a [previous article](https://fauna.com/blog/getting-started-with-fql-faunadbs-native-query-language-part-4) to do that for us.

Or, we could just do it right in our FQL query:
```
> Let(
  {
    ref: Ref(Collection("Teleportations"), "274157476972069395"),
    ts: Select(["data", "ts"], Get(Var("ref"))),
    timeDiffHours: TimeDiff(Now(), Var("ts"), "hours"),
    hoursInMinutes: Multiply(Var("timeDiffHours"), 60),
    timeDiffMinutes: TimeDiff(Now(), Var("ts"), "minutes"),
    remainingMinutes: Subtract(Var("timeDiffMinutes"), Var("hoursInMinutes"))
  },
  Format(
    "You have to wait %s hours and %s minutes",
    Var("timeDiffHours"),
    Var("remainingMinutes")
  )
)
You have to wait 246 hours and 45 minutes
```
Since we know **timeDiffHours** is rounded to the lowest value, we just need to find a way to calculate the remaining minutes. To do that we simply convert these hours into minutes (by multiplying the hours to 60) and subtract that to the total time in minutes.

##Sorting time results
Sorting index results by date or timestamps is no different than sorting by any other type of value. Let's create an index that sorts all the documents in the **Teleportations** collection by the **ts** property:
```
> CreateIndex({
  name: "all_Teleportations_by_ts",
  source: Collection("Teleportations"),
  values: [
    { field: ["data", "ts"] },
    { field: ["ref"] }
  ]
})
```
We already saw in a [previous article](https://fauna.com/blog/getting-started-with-fql-faunadbs-native-query-language-part-2) how indexes and sorting works, so I won't go into much detail here.

Here's a possible query to get the results from that index:
```
> Paginate(Match(Index("all_Teleportations_by_ts")))
{
  data: [
    [
      Time("2020-08-19T12:56:31.102631Z"),
      Ref(Collection("Teleportations"), "274138599280083456")
    ],
    [
      Time("2020-08-28T09:56:34.304009Z"),
      Ref(Collection("Teleportations"), "274157476972069395")
    ]
    // etc...
  ]
}
```
By default, FaunaDB sorts values in ascending order, and it's no different here as we're getting older results first.

If we wanted to get the most recent results first, we'd need an index with a reverse order:
```
> CreateIndex({
  name: "all_Teleportations_by_ts_desc",
  source: Collection("Teleportations"),
  values: [
    { field: ["data", "ts"], reverse: true },
    { field: ["ref"] }
  ]
})
```
##Filtering time values
There are a couple of strategies we can follow depending on our use case. Obviously, all of these techniques involve using indexes. If you're new to FQL [here's my introductory article on indexes](https://fauna.com/blog/getting-started-with-fql-faunadbs-native-query-language-part-2).

###Filter by exact date
The easiest use case is to filter by an exact date. Since our **Teleportations** documents use a timestamp we would need to add a date before we can do that.

So, let's create a simple query that updates all the documents with a date property:
```
> Map(
  Paginate(Match(Index("all_Teleportations_by_ts"))),
  Lambda(
    ["ts","ref"],
    Update(
      Var("ref"),
      {data: {date:ToDate(Var("ts"))}}
    )
  )
)
{
  data: [
    {
      ref: Ref(Collection("Teleportations"), "274138599280083456"),
      ts: 1597854857396000,
      data: {
        personRef: Ref(Collection("Pilots"), "266350546751848978"),
        status: "SCHEDULED",
        ts: Time("2020-08-19T12:56:31.102631Z"),
        date: Date("2020-08-19")
      }
    },
    // etc...
  ]
}
```
Great, so now we can create a simple index to filter by date
```
> CreateIndex({
  name: "all_Teleporations_by_date",
  source: Collection("Teleportations"),
  terms: [
    { field: [“data”, "date"]}
  ]
})
```
And here's how we'd get all the teleportations for a given date:
```
> Paginate(Match(Index("all_Teleporations_by_date"), Date("2020-08-19")))
{
  data: [Ref(Collection("Teleportations"), "274138599280083456")]
}
```
##Filter by day of the week
What if we wanted to know which teleportations happened on, say, a Wednesday?

Again, we could just add the day of the week to our documents and filter using that:
```
> Map(
  Paginate(Match(Index("all_Teleportations_by_ts"))),
  Lambda(
    ["ts","ref"],
    Update(
      Var("ref"),
      {data: {weekday: DayOfWeek(Var("ts"))}}
    )
  )
)
{
  data: [
    {
      ref: Ref(Collection("Teleportations"), "274138599280083456"),
      ts: 1597855390458000,
      data: {
        personRef: Ref(Collection("Pilots"), "266350546751848978"),
        status: "SCHEDULED",
        ts: Time("2020-08-19T12:56:31.102631Z"),
        date: Date("2020-08-19"),
        weekday: 3
      }
    },
    // etc...
  ]
}
```
[DayOfWeek()](https://docs.fauna.com/fauna/current/api/fql/functions/dayofweek) takes a timestamp and returns an integer from 1 to 7. Monday would be 1, Tuesday 2, and so on.

Now we just need to create a new index:
```
> CreateIndex({
  name: "all_Teleportations_by_weekday",
  source: Collection("Teleportations"),
  terms: [
    {field: ["data", "weekday"]}
  ]
})
```
And here's how we would find all the teleportations that happened on a Wednesday:
```
> Paginate(Match(Index("all_Teleportations_by_weekday"), 3))
{
  data: [Ref(Collection("Teleportations"), "274138599280083456")]
}
```
###Filter by a time range
**Introduction to ranges queries in FaunaDB**
Before being able to filter by a time range, we need to take a little detour to explain how range queries work in FaunaDB.

Let's create a quick collection and fill it with some documents first:
```
> CreateCollection({name: "Numbers"})

> Create(Collection("Numbers"), {data: {value: 1}})
> Create(Collection("Numbers"), {data: {value: 2}})
> Create(Collection("Numbers"), {data: {value: 3}})
// etc...
```
Let's now create an index that sorts and returns the **value** property of those documents:
```
> CreateIndex({
  name: "Numbers_by_value",
  source: Collection("Numbers"),
  values:[
    {field: ["data", "value"]}
  ]
})
```
This index is simply returning the **value** of all the documents in the collection:
```
> Paginate(Match(Index("Numbers_by_value")))
{
  data: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
}
```
This is what happens when we [useRange()](https://docs.fauna.com/fauna/current/api/fql/functions/range) with this index:
```
> Paginate(
  Range(Match(Index("Numbers_by_value")), 2, 6)
)
{
  data: [2, 3, 4, 5, 6]
}
```
Makes sense, right? **Range()** filters those index results within the bounds of 2 and 6.

There are a couple of nuances lost in this simple example though. What happens when the index returns an array instead of a single value?

Let's create some test data and an index to test this out:
```
> CreateCollection({name: "NumbersMultiple"})

> Create(Collection("NumbersMultiple"), {data: {a: 1, b: 8}})
> Create(Collection("NumbersMultiple"), {data: {a: 2, b: 4}})
// etc...

> CreateIndex({
  name: "NumbersMultiple_by_a_and_b",
  source: Collection("NumbersMultiple"),
  values: [
    {field: ["data", "a"]},
    {field: ["data", "b"]}
  ]
})
```
Check what happens when using **Range()** now:
```
> Paginate(
  Range(Match(Index("NumbersMultiple_by_a_and_b")), 2, 6)
)
{
  data: [
    [2, 4],
    [3, 50],
    [4, 0],
    [5, 6],
    [6, 9]
  ]
}
```
As you can see, **Range()** is pretty much ignoring the second value. When receiving an array, **Range()** only takes into account the first value that can be fit into the bounds and ignores the rest.

We will go into more detail on how range queries work in a future article, but we're now ready to tackle our time filtering problem.

**Filtering by a date range**
We could maybe reuse the previous **all_Teleporations_by_date** index like this:
```
> Paginate(
  Range(
    Match(Index("all_Teleporations_by_date")),
    Date("2020-01-01"),
    Date("2021-01-01")
  )
)
{
  data: []
}
```
Huh? How come that doesn't work?

If you scroll up a bit, you can see that the **all_Teleporations_by_date** index doesn't have a **values** object, so it just returns an array with references. **Range()** didn't return anything simply because it couldn't compare dates with references.

To fix this we need to create a new index with a **values** object, that returns values that **Range()** can use to filter:
```
> CreateIndex({
  name: "all_Teleporations_by_ts_range",
  source: Collection("Teleportations"),
  values: [
    { field: ["data", "ts"]},
    { field: "ref"}
  ]
})
```
And then query it:
```
> Paginate(
  Range(
    Match(Index("all_Teleporations_by_ts_range")),
    Now(),
    TimeAdd(Now(), 100, "days")
  )
)
{
  data: [
    [
      Time("2020-08-19T12:56:31.102631Z"),
      Ref(Collection("Teleportations"), "274138599280083456")
    ],
    [
      Time("2020-08-28T09:56:34.304009Z"),
      Ref(Collection("Teleportations"), "274157476972069395")
    ]
  ]
}
```
Take note that we need to pass timestamps for this to work. Otherwise, we won't get any results since the comparison wouldn't be possible:
```
> Paginate(
  Range(
    Match(Index("all_Teleporations_by_ts_range")),
    Date("2020-01-01"),
    Date("2021-01-01")
  )
)
{
  data: []
}
```
We could, of course, simply cast those dates to timestamps, so that **Range()** is comparing timestamps with timestamps:
```
> Paginate(
  Range(
    Match(Index("all_Teleporations_by_ts_range")),
    ToTime(Date("2020-01-01")),
    ToTime(Date("2021-01-01"))
  )
)
{
  data: [
    [
      Time("2020-08-19T12:56:31.102631Z"),
      Ref(Collection("Teleportations"), "274138599280083456")
    ],
    [
      Time("2020-08-28T09:56:34.304009Z"),
      Ref(Collection("Teleportations"), "274157476972069395")
    ]
  ]
}
```
##Conclusion
So that's it for today. Hopefully you learned something valuable!

In the following article of the series, we will continue our space adventure by checking out all the temporality features in FaunaDB.

If you have any questions don't hesitate to hit me up on Twitter: [@pierb](https://twitter.com/PierB)