24.3. Converting a String to a Timestamp (strptime) #
Required privileges: Postgres Pro AXE administrator only. For a full list of stored procedures and privileges, refer to Section 12.1.
Execute the following command on the Postgres Pro AXE server:
SELECT strptime('string', 'format') ON CONFLICT DO NOTHING;
Where:
string: The string that must be converted to a timestamp.format: The format of the string.Possible values:
%Y: 4-digit year (2024).%m: Month as a number (01-12).%d: Day of the month (01-31).%H: Hour (00-23).%M: Minute (00-59).%S: Second (00-59).%A: Full weekday name (Monday).%B: Full month name (January).%b: Abbreviated month name (Jan).
Postgres Pro AXE returns the converted string as a TIMESTAMP value.
Example 24.3. Executing the strptime Stored Procedure
Converting date strings in different formats to timestamps:
SELECT strptime('Jan 15, 2024', '%b %d, %Y') AS date1,
strptime('15/01/2024', '%d/%m/%Y') AS date2,
strptime('2024-01-15T14:30:00Z', '%Y-%m-%dT%H:%M:%SZ') AS iso_date;
Converting date strings from the raw_logs table:
SELECT
log_id,
strptime(timestamp_string, '%Y-%m-%d %H:%M:%S') AS parsed_time,
message
FROM raw_logs;