Search code examples
sqlpostgresqldatetimestampjulian-date

creating timestamp from columns in postgres

i have 2 rows in a column, one feeding me the julian date (number of days since Jan 1, 1970, with that day being 1), and the second column is the number of minutes past midnight of the current day (why it was done this way, i have no idea).

i would like to get my sql query to create a timestamp out of these two columns.

if i had access to the data ahead of time, i could do something like SELECT timestamp '1970-01-01 00:00:00' + INTERVAL 'X DAYS' + INTERVAL 'Y MINUTES' AS my_time FROM mytable, but since X and Y are actual members of the table i'm querying, it's not that simple.

anyone have any suggestions?

essentially, i'm looking for the equivalent [legal] solution:

SELECT timestamp '1970-01-01 00:00:00' + INTERVAL 'my_col1 DAYS' + INTERVAL 'my_col2 MINUTES' AS my_time FROm mytable

i can get my server-side code to do it for me, but i would love it if i could build it into my query.

Solution

  • You can construct the interval strings and then cast them to the interval type:

    SELECT
    timestamp '1970-01-01 00:00:00' +
    cast(my_col1 || ' DAYS' AS interval) +
    cast(my_col2 || ' MINUTES' AS interval) my_time FROM mytable