1

In the end, I'm shooting for the column to have values formatted exactly like this: 2022-01-05 07:00:00.000 UTC (YYYY-MM-DD HH.MM.SS.fff UTC)

From the database we're working with, the table has two columns of interest.

  1. card_day which is a datetime, but the only thing we care about is the actual date. (ex: 2022-01-06 00:00:00.000, the time is always 00:00:00.000)
  2. l_time which is a varchar containing the hour and minute. (ex: 03:30)

I want to combine both of these into a new column with the value (using the example ^):

2022-01-06 03:30:00.000 UTC

I did find a way to do this which seems to work for now:

to_iso8601(DATE(card_day)) || ' ' || l_time || ':00.000 UTC' as crazy_formatted_timestamp,

So I'm basically just converting the pieces into strings and then concattenating everything together including hard-coding the seconds and timezone piece.

I feel like there has to be a better way, but I'm struggling to find the right way to do this with Presto SQL. Are there any minor or major improvements I can make here to clean things up?

Help would be much appreciated!

CustardBun
  • 3,457
  • 8
  • 39
  • 65
  • Does the [RTFM](https://prestodb.io/docs/current/functions/datetime.html) help? – Rafael Herscovici Jan 06 '22 at 01:06
  • Does this answer your question? [Presto SQL - Converting a date string to date format](https://stackoverflow.com/questions/39880540/presto-sql-converting-a-date-string-to-date-format) – Rafael Herscovici Jan 06 '22 at 01:07
  • Hi, I was looking at those before I asked this question and that’s how I found out about the to_iso function, though now that I look at my solution again, it’d probably be the same without if it doesn’t care if I try to concat a date with a string. The link about date string to date format would help if I was trying to convert a date string to a date but I was more specifically trying to see if there was a better way of doing what I am currently doing (combining a date, a string for time, and trying to make a particular format from it whether it’s a string or a timestamp). Thanks though – CustardBun Jan 06 '22 at 05:25

0 Answers0