loading...
Cover image for Core FQL concepts, part 1: Working with dates and times
Fauna, Inc.

Core FQL concepts, part 1: Working with dates and times

fauna_admin profile image Fauna Originally published at fauna.com ・11 min read

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.

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 to store a calendar date
  • 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() function:

Date("2020-08-15")
Enter fullscreen mode Exit fullscreen mode

Similarly, we can create a new Timestamp value by passing an UTC ISO time and date string to the Time() function:

Time("2020-08-15T18:39:45Z")
Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode

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() to create a timestamp based on a UNIX time:

> Epoch(1597533145964, "millisecond")
Time("2020-08-15T23:12:25.964Z")
Enter fullscreen mode Exit fullscreen mode

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() 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")
Enter fullscreen mode Exit fullscreen mode

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")
}
Enter fullscreen mode Exit fullscreen mode

Converting between Date and Timestamp types

To convert a Timestamp to a Date we use the 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")
Enter fullscreen mode Exit fullscreen mode

Likewise, to convert a Date to a Timestamp we use the ToTime() function:

> ToTime(Date("2020-08-15"))
Time("2020-08-15T00:00:00Z")
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

We can even use LT() and LTE() to know which date is "larger", or more recent:

> LT(Now(), Date("1970-11-05"))
true
Enter fullscreen mode Exit fullscreen mode

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() 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
Enter fullscreen mode Exit fullscreen mode

It's also very common to use ISO date strings:

> Format('%t', Now())
2020-08-20T16:13:13.654455Z
Enter fullscreen mode Exit fullscreen mode

You might also want to print the date in some other format than YYYY-MM-DD:

> Format('%tD', Now())
08/20/20
Enter fullscreen mode Exit fullscreen mode

And again, in the European format:

> Format('%td/%tm/%ty', Now(), Now(), Now())
20/08/20
Enter fullscreen mode Exit fullscreen mode

As I said, Format() is extremely powerful. Check the docs 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'
}
Enter fullscreen mode Exit fullscreen mode

Let’s also create a new collection to track the pilots:

> CreateCollection({name:"Pilots"})
{
  ref: Collection("Pilots"),
  ts: 1597715790726000,
  history_days: 30,
  name: 'Pilots'
}
Enter fullscreen mode Exit fullscreen mode

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")
  }
}
Enter fullscreen mode Exit fullscreen mode

This is the interesting part:

TimeAdd(Now(), 10, "days")
Enter fullscreen mode Exit fullscreen mode

The 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 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")
  }
}
Enter fullscreen mode Exit fullscreen mode

Quick tip: The 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() which works exactly like TimeAdd():

TimeSubtract(Now(), 4, "hours")
Enter fullscreen mode Exit fullscreen mode

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() to calculate differences between two times:

> TimeDiff(
  Date("2020-08-15"),
  Date("2020-08-20"),
  "days"
)
5
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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 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
Enter fullscreen mode Exit fullscreen mode

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"] }
  ]
})
Enter fullscreen mode Exit fullscreen mode

We already saw in a previous article 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...
  ]
}
Enter fullscreen mode Exit fullscreen mode

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"] }
  ]
})
Enter fullscreen mode Exit fullscreen mode

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.

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...
  ]
}
Enter fullscreen mode Exit fullscreen mode

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"]}
  ]
})
Enter fullscreen mode Exit fullscreen mode

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")]
}
Enter fullscreen mode Exit fullscreen mode

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...
  ]
}
Enter fullscreen mode Exit fullscreen mode

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"]}
  ]
})
Enter fullscreen mode Exit fullscreen mode

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")]
}
Enter fullscreen mode Exit fullscreen mode

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...
Enter fullscreen mode Exit fullscreen mode

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"]}
  ]
})
Enter fullscreen mode Exit fullscreen mode

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]
}
Enter fullscreen mode Exit fullscreen mode

This is what happens when we useRange() with this index:

> Paginate(
  Range(Match(Index("Numbers_by_value")), 2, 6)
)
{
  data: [2, 3, 4, 5, 6]
}
Enter fullscreen mode Exit fullscreen mode

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"]}
  ]
})
Enter fullscreen mode Exit fullscreen mode

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]
  ]
}
Enter fullscreen mode Exit fullscreen mode

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: []
}
Enter fullscreen mode Exit fullscreen mode

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"}
  ]
})
Enter fullscreen mode Exit fullscreen mode

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")
    ]
  ]
}
Enter fullscreen mode Exit fullscreen mode

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: []
}
Enter fullscreen mode Exit fullscreen mode

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")
    ]
  ]
}
Enter fullscreen mode Exit fullscreen mode

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

Discussion

pic
Editor guide