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.to_char(
    func.date_trunc('week', ((table.Date) - 6)),
    'MON-DD'
)

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.


func.age

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

func.clock_timestamp

Current date and time (changes during statement execution)

func.current_date

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

func.current_time

Returns a current time value based on the time zone

func.current_timestamp

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

func.date_part

Returns a 'date' value expression through its subfields

func.date_trunc

Truncate input value to specified precision

func.extract

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

func.isfinite

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

func.justify_days

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

func.justify_hours

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

func.justify_interval

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

func.now

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

func.statement_timestamp

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

func.timeofday

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

func.transaction_timestamp

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

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