How do I format a datetime on sqlite with timezone?

Question

for example I have this date jan 5, 2010 14:00 wednesday gmt-8 how do I implement this so I can save it with timezone?,

I can save this using nsformatter with yyyy-MM-dd hh:mm and saves as

2010-01-05 14:00

but how about w/ timezone?

(it should be saved in datetime format.. not text format)

Try like this ... `new Date("jan 5, 2010 14:00 wednesday gmt-8")` — Soundar, Apr 15 '14 at 06:13

score 1 · Answer 1 · answered Apr 15 '14 at 06:10

1

You can use [format setDateFormat:@"yyyy-MM-dd hh:mm a"] for timezone it will give output like this "2010-01-05 02:00 PM"

or [format setDateFormat:@"MMM dd, yyyy hh:mm a"]

answered Apr 15 '14 at 06:10

Nisha

354
2
19

yes thanks. but thats not the answer to my question what I want to be inserted (on sqlite) is like this: 2010-01-05 14:00 GMT-8 (in datetime format)... something like that – user3517855 Apr 15 '14 at 06:13

score 0 · Answer 2 · edited May 23 '17 at 12:00

Put the timezone in another column and you won't break the limited existing date functionality of SQLLITE. Its really easy to add columns to sqllite with an alter table statement. Android SQLite Database, WHY drop table and recreate on upgrade

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

thinking about this I would actually never store a timezone on every row of a table. I would however store a timezone in a setting somewhere!!! — danny117, Oct 19 '16 at 13:42

How do I format a datetime on sqlite with timezone?

2 Answers2