Date and Time

Basic date functions like current-date, now, and timeofday

Dates and times can cause some additional mental gymnastics at times due to timezones, offsets, varying days and weeks within months, and more. While we can't solve the problem of how we measure and track time, there are a few ways to extract and manipulate date and time related data.

Extracting the week from a date

This will extract the week in the MON-DD format:

    func.date_trunc('week', ((table.Date) - 6)),

The example above assumes you have a date field in your data called Date. In this example, we are subtracting six days from that date but that may not be necessary in your specific use case. It is shown here to help if you do need to add or subtract days.

This uses the to_char and the date_trunc methods.


Subtract arguments, producing a “symbolic” result that uses years and months, rather than just days


Current date and time (changes during statement execution)


Returns current date value based on the start of the current transaction


Returns a current time value based on the time zone


Returns a current date and timestamp based on the local time zone and start of current transaction


Returns a 'date' value expression through its subfields


Truncate input value to specified precision


Retrieves subfields such as year or hour from date/time values


This function is used to determine whether the numeric value is finite


Adjust interval so 30-day time periods are represented as months


Adjust interval so 24-hour time periods are represented as days


This function is used to adjust interval using justify_days and justify_hours, with additional sign adjustments

Returns current date and time based on the database server's time zone setting


This function is used to get current date and time (start of current transaction)


This function is used to get current date and time (like clock_timestamp, but as a text string)


Returns the current date and time (including the time zone offset), at the start of the current transaction. Equivalent to ''

Last modified November 27, 2023 at 12:56 PM EST: Restructured the file structure/a few changes (f6c58b8)