EdgeDB 1.0 is launching on Feb 10th! Watch the live event.
Claim your ticket
Easy EdgeDB · Chapter 5

DatetimeDescribing Types

Poor Jonathan is not having much luck. Here’s what happens to him in this chapter:

During the day, Jonathan decides to try to explore the castle but too many doors and windows are locked. He doesn’t know how to get out, and wishes he could at least send Mina a letter. He pretends that there is no problem, and keeps talking to Dracula during the night. One night he sees Dracula climb out of his window and down the castle wall, like a snake. Now he is very afraid, and knows that Dracula is not human. A few days later he breaks one of the doors and finds another part of the castle. The room is very strange and he feels sleepy. When he opens his eyes, he sees three vampire women next to him. He is attracted to and afraid of them at the same time. He wants to kiss them, but knows that he will die if he does. They come closer, and he can’t move…

Since Jonathan was thinking of Mina back in London, let’s learn about std::datetime because it uses time zones. To create a datetime, you can just cast a string in ISO 8601 format with <datetime>. That format looks like this:


And an actual date looks like this.


The T inside there is just a separator, and the Z at the end stands for “zero timeline”. That means that it is 0 different (offset) from UTC: in other words, it is UTC.

One other way to get a datetime is to use the to_datetime() function. Here is its signature, which shows that there are six ways to make a datetime with this function depending on how you want to make it. EdgeDB will know which one of the six you have chosen depending on what input you give it.

By the way, you’ll notice one unfamiliar type inside called a decimal type. This is a float with “arbitrary precision”, meaning that you can give it as many numbers after the decimal point as you want. This is because float types on computers become imprecise after a while thanks to rounding errors. This example shows it:

SELECT 6.777777777777777; # Good so far
SELECT 6.7777777777777777; # Add one more digit...
{6.777777777777778} # Where did the 8 come from?!

If you want to avoid this, add an n to the end to get a decimal type which will be as precise as it needs to be.

SELECT 6.7777777777777777n;
SELECT 6.7777777777777777777777777777777777777777777777777n;

Meanwhile, there is a bigint type that also uses n for an arbitrary size. That’s because even int64 has a limit: it’s 9223372036854775807.

SELECT 9223372036854775807; # Good so far...
SELECT 9223372036854775808; # But add 1 and it will fail
ERROR: NumericOutOfRangeError: std::int64 out of range

So here you can just add an n and it will create a bigint that can accommodate any size.

SELECT 9223372036854775808n;

Now that we know all the numeric types, let’s get back to the six signatures for the std::to_datetime function:

std::to_datetime(s: str, fmt: OPTIONAL str = {}) -> datetime
std::to_datetime(local: cal::local_datetime, zone: str) -> datetime
std::to_datetime(year: int64, month: int64, day: int64, hour: int64, min: int64, sec: float64, timezone: str) -> datetime
std::to_datetime(epochseconds: decimal) -> datetime
std::to_datetime(epochseconds: float64) -> datetime
std::to_datetime(epochseconds: int64) -> datetime

The easiest is probably the third if you find ISO 8601 unfamiliar or you have a bunch of separate numbers to make into a date. With this, our game could have a function that generates integers for times that then use to_datetime() to get a proper time stamp.

Let’s imagine that it’s May 12. It’s a bright morning at 10:35 in Castle Dracula. The sun is up, Dracula is asleep somewhere, and Jonathan is trying to use the time during the day to escape to send Mina a letter. In Romania the time zone is ‘EEST’ (Eastern European Summer Time). We’ll use to_datetime() to generate this. We won’t worry about the year, because the story takes place in the same year - we’ll just use 2020 for convenience. We type this:

SELECT to_datetime(2020, 5, 12, 10, 35, 0, 'EEST');

And get the following output:


The 07:35:00 part shows that it was automatically converted to UTC, which is London where Mina lives.

We can also use this to see the duration between events. EdgeDB has a duration type that you can get by subtracting a datetime from another one. Let’s practice by calculating the exact number of seconds between one date in Central Europe and another in Korea:

SELECT to_datetime(2020, 5, 12, 6, 10, 0, 'CET') - to_datetime(2000, 5, 12, 6, 10, 0, 'KST');

This takes May 12 2020 6:10 am in Central European Time and subtracts May 12 2000 6:10 in Korean Standard Time. The result is: {<duration>'175328:00:00'}.

Now let’s try something similar with Jonathan in Castle Dracula again, trying to escape. It’s May 12 at 10:35 am. On the same day, Mina is in London at 6:10 am, drinking her morning tea. How many seconds passed between these two events? They are in different time zones but we don’t need to calculate it ourselves; we can just specify the time zone and EdgeDB will do the rest:

SELECT to_datetime(2020, 5, 12, 10, 35, 0, 'EEST') - to_datetime(2020, 5, 12, 6, 10, 0, 'UTC');

The answer is 1 hour and 25 minutes: {<duration>'1:25:00'}.

To make the query easier for us to read, we can also use the WITH keyword to create variables. We can then use the variables in SELECT below. We’ll make one called jonathan_wants_to_escape and another called mina_has_tea, and subtract one from another to get a duration. With variable names it is now a lot clearer what we are trying to do:

  jonathan_wants_to_escape := to_datetime(2020, 5, 12, 10, 35, 0, 'EEST'),
  mina_has_tea := to_datetime(2020, 5, 12, 6, 10, 0, 'UTC'),
SELECT jonathan_wants_to_escape - mina_has_tea;

The output is the same: {<duration>'1:25:00'}. As long as we know the timezone, the datetime type does the work for us when we need a duration.

Besides subtracting a datetime from another datetime, you can also just cast to make a duration. To do this, just write the number followed by the unit: microseconds, milliseconds, seconds, minutes, or hours. It will return a number of seconds, or a more precise unit if necessary. For example, SELECT <duration>'2 hours'; will return {<duration>'2:00:00'}, and SELECT <duration>'2 microseconds'; will return {<duration>'0:00:00.000002'}.

You can include multiple units as well. For example:

SELECT <duration>'6 hours 6 minutes 10 milliseconds 678999 microseconds';

This will return {<duration>'6:06:00.688999'}.

EdgeDB is pretty forgiving when it comes to inputs when casting to a duration, and will ignore plurals and other signs. So even this horrible input will work:

SELECT <duration>'1 hours, 8 minute ** 5 second ()()()( //// 6 milliseconds' -
  <duration>'10 microsecond 7 minutes %%%%%%% 10 seconds 5 hour';

The result: {<duration>'-3:59:04.99401'}.

Our MinorVampire type extends Person, and so does Vampire. Types can continue to extend other types, and they can extend more than one type at the same time. The more you do this, the more annoying it can be to try to combine it all together in your mind. This is where DESCRIBE can help, because it shows exactly what any type is made of. There are three ways to do it:

  • DESCRIBE TYPE MinorVampire - this will give the DDL (data definition language) description of a type. DDL is a lower level language than SDL, the language we have been using. It is less convenient for schema, but is more explicit and can be useful for quick changes. We won’t be learning any DDL in this course but later on you might find it useful sometimes. For example, with it you can quickly create functions without needing to do an explicit migration. And if you understand SDL it will not be hard to pick up some tricks in DDL.

(Note though the word explicit there: using DDL still results in a migration, just an implicit one. In other words, a migration happens without calling it a migration. It’s sort of a quick and dirty way to make changes but for the most part proper migration tools with SDL schema is the preferred way to go.)

Now back to DESCRIBE TYPE which gives the results in DDL. Here’s what our MinorVampire type looks like:

  'CREATE TYPE default::MinorVampire EXTENDING default::Person {
    CREATE REQUIRED LINK master -> default::Vampire;

The CREATE keyword shows that it’s a series of quick commands, which is why the order is important. In other words, SDL is declarative (it declares what something will be without worrying about order), while DDL is imperative (it’s a series of commands to change the state). Also, because it only shows the DDL commands to create it, it doesn’t show us all the Person links and properties that it extends. So we don’t want that. The next method is:

  • DESCRIBE TYPE MinorVampire AS SDL - same thing, but in SDL.

The output is almost the same too, just the SDL version of the above. It’s also not enough information for what we want now:

  'type default::MinorVampire extending default::Person {
    required link master -> default::Vampire;

You’ll notice that it’s basically the same as our SDL schema, just a bit more verbose and detailed: type default::MinorVampire instead of type MinorVampire, and so on.

  • The third method is DESCRIBE TYPE MinorVampire AS TEXT. This is what we want, because it shows everything inside the type, including stuff from the types that it extends. Here’s the output:

  'type default::MinorVampire extending default::Person {
    required single link __type__ -> schema::Type {
        readonly := true;
    optional single link lover -> default::Person;
    required single link master -> default::Vampire;
    optional multi link places_visited -> default::Place;
    required single property id -> std::uuid {
        readonly := true;
    required single property name -> std::str;

(Note: AS TEXT doesn’t include constraints and annotations. To see those, add VERBOSE at the end: DESCRIBE TYPE MinorVampire AS TEXT VERBOSE. You’ll learn about annotations in Chapter 14.)

The parts that say readonly := true we don’t need to worry about, as they are automatically generated (and we can’t touch them). For everything else, we can see that we need a name and a master, and could add a lover and places_visited for these MinorVampires.

And for a really long output, try typing DESCRIBE SCHEMA or DESCRIBE MODULE default (with AS SDL or AS TEXT if you want). You’ll get an output showing the whole schema we’ve built so far.

So if TYPE comes after DESCRIBE for types and MODULE after DESCRIBE for modules, then what about links and all the rest? Here’s the full list of keywords that can come after describe: OBJECT, ANNOTATION, CONSTRAINT, FUNCTION, LINK, MODULE, PROPERTY, SCALAR TYPE, TYPE. If you don’t want to remember them all, just go with OBJECT: it will match anything inside your schema (except modules).

Here is all our code so far up to Chapter 5.

Practice Time
  1. What do you think SELECT to_datetime(3600); will return, and why?

    Hint: check the function signatures above and see which one EdgeDB will pick when you enter 3600.

    Show answer
  2. Will SELECT <int16>9 + 1.06n IS decimal; work? And if it does, will it return {true}?

    Show answer
  3. How many seconds went by between 5:00 am on Christmas Day 2003 in Turkmenistan (TMT) and 7:00 pm on New Year’s Eve for the same year in Uzbekistan (UZT)?

    Show answer
  4. How would you write the same query using WITH for each of the two times?

    Show answer
  5. What’s the best way to describe a type if you only want to see how you wrote it?

    Show answer

Up next: One of the women vampires to her sisters: “He is young and strong; there are kisses for us all…”