[zeroxfe]

I asked Google Gemini to do this, and it gave me a seemingly convincing response. Anyone know if it's right?

Prompt:

  translate the SQL statement "select TRUNC(SYSDATE, 'month') from dual" to a PostgreSQL dialect

Response (formatting removed):

  Here is the equivalent SQL statement in PostgreSQL for "select TRUNC(SYSDATE, 'month') from dual" in Oracle:

    SELECT DATE_TRUNC('month', CURRENT_DATE) AT TIME ZONE 'UTC'

  Use code with caution.

  In PostgreSQL:

    DATE_TRUNC is the function used to truncate a date value to a specified unit.

    CURRENT_DATE is a function that returns the current date.

    AT TIME ZONE 'UTC' is used to specify that the truncation should be done in Coordinated Universal Time (UTC). This is optional, but it's a good practice to be explicit about the time zone to avoid any ambiguity.

  The dual table is a dummy table specific to Oracle that is used in simple SQL statements. 

  PostgreSQL doesn't require a table reference in this case.

[peter_l_downs]

I view a comment like yours as essentially spam and of negative value. I'm not sure if we (vaguely, hn forum community) have agreed on the etiquette here, but I have to ask why you would bother sharing an AI-generated response if you're not sure if it's correct. What are you getting at? How could this be helpful?

[saurik]

I am often picky about my/our time being wasted by low-effort comments but in this case I actually was myself coming into this thread assuming the actual tool being discussed was itself going to be an LLM-based solution; and, when it wasn't, all I was thinking was "aren't we in the future? isn't this just something people are going to ask an AI to handle now?".

Hell: a friend of mine claims he asked Claude (using some plug-in in his IDE) to port his app from Firebase all the way to PostgreSQL and it just did it... and it mostly worked well enough to save him a ton of time typing and looking up syntax for stuff, and I think it is now in production?

I do agree, though--and maybe this was your core point--that maybe this "let's ask Gemini" comment should be left by someone who would also know if the answer was correct rather than just saying "no clue but". Although, honestly, even having the output seems useful here (and again: I am someone constantly unhappy about people asking questions they can Google and other such filler content).

[irq-1]

I agree: the comment was on-topic, included technical information (dual table?), and AI accuracy/usefulness is of interest to most readers. Maybe in a year I'll think such a comment is bad, but not today.

[febed]

> a friend of mine claims he asked Claude (using some plug-in in his IDE) to port his app from Firebase all the way to PostgreSQL and it just did it

In now interested in your friends setup. Do you have more details?

[saurik]

"I’ve been using Phind + their VS Code plugin" ... "I got them to add Claude 3 Opus support"

[parhamn]

I think it's really reasonable. When I was going through the readme I was (half heartedly) interested in seeing an OPENAI_KEY config variable in the readme.

[zeroxfe]

Apologies, I was trying to imply that LLMs may be more practical to solve these kinds of problems than hand-built translators (assuming the primary purpose is for engineers using this during development.)

[scarface_74]

A transpiler will be consistent and can be correct.

An LLM based solution can be correct but won’t be consistent.

I would much rather a tool be consistent and always incorrect in the same way than inconsistent and sometimes correct. I can create test cases and correct the underlying code. I can’t do that with an LLM

[zeroxfe]

> A transpiler will be consistent and can be correct.

No disagreement. My point is that it's impractically hard to build especially given the scope and complexity of the SQL dialects of major databases.

[irq-1]

Looks like `AT TIME ZONE 'UTC'` should not be used, but I don't think it's illegal syntax.

https://www.postgresql.org/docs/current/datatype-datetime.ht...

> PostgreSQL endeavors to be compatible with the SQL standard definitions for typical usage. However, the SQL standard has an odd mix of date and time types and capabilities...

> Although the date type cannot have an associated time zone, the time type can.

(emphasis mine)

> To address these difficulties, we recommend using date/time types that contain both date and time when using time zones.