If you need to calculate the number of seconds that have passed since a given date and time, you can use the UNIXEPOCH() function.
Note that this function was introduced in SQLite 3.38.0, so it will only work if you’re using SQLite 3.38.0 or later.
Continue readingIn SQLite, we can use the DATE() function to add a given number of days to a date.
If we’re dealing with datetime values, we can use the DATETIME() function.
Here are two methods for returning the Julian Day in SQLite.
The Julian Day is the fractional number of days since noon in Greenwich on November 24, 4714 B.C. It’s the continuous count of days since the beginning of the Julian period, and is used primarily by astronomers, and in software for easily calculating elapsed days between two events.
Continue readingIn PostgreSQL, we can use the - operator to subtract one or more years from a date.
In PostgreSQL, we can use the + operator to add one or more years to a date.
We can use SQLite’s DATE() function to return the date of the first instance of a given day of a given year. Therefore, we can use it to return the first Monday of a given year. We can also use it to return the first Tuesday, Wednesday, Thursday, Friday, etc.
We can use DATETIME() if we want a datetime value to be returned.
In PostgreSQL, we can use the + operator to add one or more weeks to a date. We have a few options when it comes to specifying the actual number of weeks.
The unix timestamp is the number of seconds since 1970-01-01 00:00:00 UTC. SQLite provides us with a couple of ways to get the unix timestamp.
Continue readingSQLite’s DATE() function provides us with the ability to return the date of the first instance of a given day of a given month. Therefore, we can use it to return the first Monday of a given month. We can also use it to return the first Tuesday, Wednesday, Thursday, Friday, etc.
We can use DATETIME() if we want a datetime value to be returned.
If you have a Unix timestamp, you can use SQLite’s DATETIME() function with the unixepoch modifier to compute the actual date and time.
You can alternatively use the DATE() function if you only need the date to be returned. And it’s also possible to use the TIME() function to return just the time portion.