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

This series will assume you have a grasp on the basics. If you're new to Fauna and/or FQL here's my introductory series on FQL .

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

Date and time basics

Fauna 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")

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")

Timestamps in Fauna 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() 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() to create a new Timestamp (the Fauna 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() 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() 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() and 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 Fauna, 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

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 for all of the available options to format strings.

Time operations

Fauna 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: 'eleportations'
}

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() 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")
  }
}

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")

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

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 Fauna 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 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 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, Fauna 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 .

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() 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 Fauna

Before being able to filter by a time range, we need to take a little detour to explain how range queries work in Fauna.

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() 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 Fauna.

If you have any questions don't hesitate to hit me up on Twitter: @pierb

Related posts

Start for free

Request demo

Table of Contents

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

In this article:

Date and time basics

Converting between Date and Timestamp types

Comparing dates

Printing dates and times

Time operations

Addition

Subtraction

Calculating time offsets

Sorting time results

Filtering time values

Filter by exact date

Filter by day of the week

Filter by a time range

Introduction to ranges queries in Fauna

Filtering by a date range

Conclusion

Share this post

Subscribe to Fauna's newsletter

Related posts

Start for free.css-11zz33f{display:-webkit-inline-box;display:-webkit-inline-flex;display:-ms-inline-flexbox;display:inline-flex;margin-left:8px;}

Request demo

Table of Contents

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

In this article:.css-l70tfw{box-sizing:border-box;margin:0;min-width:0;display:-webkit-inline-box;display:-webkit-inline-flex;display:-ms-inline-flexbox;display:inline-flex;padding:0;opacity:0;-webkit-transition:all .2s;transition:all .2s;font-size:20px;pointer-events:none;margin-left:8px;}

Date and time basics

Converting between Date and Timestamp types

Comparing dates

Printing dates and times

Time operations

Addition

Subtraction

Calculating time offsets

Sorting time results

Filtering time values

Filter by exact date

Filter by day of the week

Filter by a time range

Introduction to ranges queries in Fauna

Filtering by a date range

Conclusion

Share this post

Subscribe to Fauna's newsletter

Start for free

In this article: