-1

In a database I work on, most of the datetimes I store are converted to and from UTC so all of my datetime columns end in UTC such as CanceledUTC.

I have a situation where I need to store a "local" time and want to signify that it is a local time in the column name.

Is there a convention for signifying local datetime variable names?

I know that storing "local" datetimes is a bad practice so I'm also considering storing it as a string, but that's not the question here.

  • You mean the column names, right? – Lightness Races with Monica Nov 13 '15 at 20:08
  • @LightnessRacesinOrbit Haha, yeah. I try and switch up my words when asking questions so other people searching can find it because this question doesn't just apply to column names. – Zach Mierzejewski Nov 13 '15 at 20:15
  • @ZachMierzejewski: What else does it apply to? I would advise sticking with proper terminology so that we know what you're talking about, rather than "switching up" your words... – Lightness Races with Monica Nov 13 '15 at 20:26
  • 1
    @ZachMierzejewski: Then your chosen terminology "column" was incorrect for both your current use case, and your future use case! lol – Lightness Races with Monica Nov 13 '15 at 20:37
5

Nothing.

The name of the column should be CancelledTime or CancelledAt, and the column should have type TIMESTAMP. It will be stored timezone-agnostically, but automatically have the timezone of the session taken into consideration when values are inserted and retrieved.

If that is not possible for some reason, then fine make it a DATETIME … but give it a COMMENT line that indicates the field values are local time representations. This really seems like a bad idea, though: local time when? Insert before daylight savings, retrieve after daylight savings → you're hosed.

Certainly, though, information about the data representation doesn't belong in the field name. What the data is belongs in the field name. And your data is (presumably) "the time at which a thing was cancelled".

Not the answer you're looking for? Browse other questions tagged or ask your own question.